Zamenjava množičnega besedila v Power Query s funkcijo List.Accumulate

Kako hitro in v velikem obsegu nadomestiti besedilo po seznamu referenc s formulami - to smo že uredili. Zdaj pa poskusimo to narediti v Power Queryju.

Kot se pogosto zgodi opravlja ta naloga je veliko lažja od razlage zakaj deluje, ampak poskusimo narediti oboje 🙂

Imamo torej dve "pametni" dinamični tabeli, ustvarjeni iz običajnih obsegov z bližnjico na tipkovnici Ctrl+T ali ekipa Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo):

Poklical sem prvo mizo datum, druga tabela – Imenikz uporabo polja Ime tabele (ime tabele) tab Konstruktor (Oblikovanje).

Naloga: zamenjajte naslove v tabeli datum vse pojavitve iz stolpca Najti Priročnik njihovim ustreznim pravilnim primerkom iz stolpca Namestnik. Preostalo besedilo v celicah naj ostane nedotaknjeno.

Korak 1. Naložite imenik v Power Query in ga spremenite v seznam

Ko nastavite aktivno celico na poljubno mesto v referenčni tabeli, kliknite zavihek datum (Datum)ali na zavihku poizvedba o moči (če imate staro različico Excela in ste Power Query namestili kot dodatek na ločenem zavihku) na gumb Iz tabele/razpona (Iz tabele/razpona).

Referenčna tabela bo naložena v urejevalnik poizvedb Power Query:

Da ne bi motili, samodejno dodan korak spremenjeni tip (Spremenjena vrsta) na desni plošči lahko uporabljene korake varno izbrišete in ostane samo korak vir (Vir):

Zdaj, za izvedbo nadaljnjih transformacij in zamenjav, moramo to tabelo spremeniti v seznam (seznam).

Lirska digresija

• Tabela je dvodimenzionalni niz, sestavljen iz več vrstic in stolpcev.
• Zapis (Zapis) – enodimenzionalni matrični niz, sestavljen iz več polj-elementov z imeni, npr [Ime = “Maša”, Spol = “ž”, Starost = 25]
  
• Seznam – enodimenzionalni niz-stolpec, sestavljen iz več elementov, npr {1, 2, 3, 10, 42} or { "Vera upanje ljubezen" }
  

Za rešitev našega problema nas bo zanimal predvsem tip Seznam.

Trik je v tem, da elementi seznama v Power Queryju niso lahko samo banalne številke ali besedilo, ampak tudi drugi seznami ali zapisi. V tako zapleten seznam (seznam), sestavljen iz zapisov (zapisov), moramo obrniti naš imenik. V sintaktični notaciji Power Query (vnosi v oglatih oklepajih, seznami v zavitih oklepajih) bi to izgledalo takole:

{

    [ Najdi = “St. Petersburg", Zamenjaj = "St. Petersburg" ] ,

    [ Najdi = “St. Petersburg", Zamenjaj = "St. Petersburg" ] ,

    [ Find = “Peter”, Replace = “St. Petersburg" ] ,

in tako naprej

}

Takšna transformacija se izvede s posebno funkcijo jezika M, vgrajenega v Power Query – Table.ToRecords. Če jo želite uporabiti neposredno v vrstici s formulami, dodajte to funkcijo tamkajšnji kodi koraka vir.

Bilo je:

Po:

Po dodajanju funkcije Table.ToRecords se bo videz naše tabele spremenil – spremenila se bo v seznam zapisov. Vsebino posameznih zapisov lahko vidite na dnu podokna s klikom v ozadju celice poleg katere koli besede Record (vendar ne z eno besedo!)

Poleg naštetega je smiselno dodati še eno potezo – predpomniti (medpomniti) naš ustvarjeni seznam. To bo prisililo Power Query, da enkrat naloži naš iskalni seznam v pomnilnik in ga ne bo ponovno izračunal, ko bomo pozneje dostopali do njega, da bi ga zamenjali. Če želite to narediti, zavijte našo formulo v drugo funkcijo – List.Buffer:

Tako predpomnjenje bo dalo zelo opazno povečanje hitrosti (za nekajkrat!) z veliko količino začetnih podatkov, ki jih je treba izbrisati.

S tem je priprava priročnika zaključena.

Ostaja še klik Domov – Zapri in naloži – Zapri in naloži na… (Domov — Zapri&Naloži — Zapri&Naloži na..), izberite možnost Samo ustvarite povezavo (Ustvari samo povezavo) in se vrnite v Excel.

2. korak. Nalaganje podatkovne tabele

Tukaj je vse banalno. Kot prej pri referenčni knjigi, pridemo do katerega koli mesta v tabeli, kliknemo na zavihek datum Gumb Iz tabele/razpona in našo mizo datum pride v Power Query. Samodejno dodan korak spremenjeni tip (Spremenjena vrsta) lahko tudi odstranite:

Z njim ni treba izvajati posebnih pripravljalnih dejanj in preidemo na najpomembnejšo stvar.

3. korak. Izvedite zamenjave s funkcijo List.Accumulate

Dodajmo izračunani stolpec v našo podatkovno tabelo z ukazom Dodajanje stolpca – stolpec po meri (Dodaj stolpec — stolpec po meri): in v okno, ki se odpre, vnesite ime dodanega stolpca (npr. popravljen naslov) in našo čarobno funkcijo Naštej.Akumuliraj:

Ostaja še klik OK – in dobimo stolpec z opravljenimi zamenjavami:

Upoštevajte, da:

• Ker je Power Query občutljiv na velike in male črke, ni bilo zamenjave v predzadnji vrstici, ker imamo v imeniku »SPb«, ne »SPb«.
• Če je v izvornih podatkih več podnizov, ki jih je treba zamenjati hkrati (na primer, v 7. vrstici morate zamenjati tako »S-Pb« kot »Prospekt«), potem to ne povzroča težav (za razliko od zamenjave s formulami iz prejšnja metoda).
• Če v izvornem besedilu (9. vrstica) ni ničesar za zamenjavo, potem ne pride do napak (za razliko od, spet, zamenjave s formulami).

Hitrost takšne zahteve je zelo, zelo spodobna. Na primer, za tabelo začetnih podatkov z velikostjo 5000 vrstic je bila ta poizvedba posodobljena v manj kot sekundi (brez medpomnjenja, mimogrede, približno 3 sekunde!)

Kako deluje funkcija List.Accumulate

Načeloma bi lahko bil to konec (jaz za pisanje, vi za branje) tega članka. Če želite ne le biti sposobni, ampak tudi razumeti, kako deluje "pod pokrovom", potem se boste morali potopiti nekoliko globlje v zajčjo luknjo in se ukvarjati s funkcijo List.Accumulate, ki je opravila vso množično zamenjavo delo za nas.

Sintaksa te funkcije je:

=List.Accumulate(Seznam, seme, Akumulator)

Kje

• Seznam je seznam, katerega elemente ponavljamo. 
• seme – začetno stanje
• Akumulator – funkcija, ki izvede neko operacijo (matematično, tekstovno ipd.) na naslednjem elementu seznama in zbere rezultat obdelave v posebni spremenljivki.
  

Na splošno sintaksa za pisanje funkcij v Power Query izgleda takole:

(argument1, argument2, … argumentN) => nekaj dejanj z argumenti

Na primer, funkcijo seštevanja bi lahko predstavili kot:

(a, b) => a + b

Za List.Accumulate ima ta funkcija zbiralnika dva zahtevana argumenta (lahko ju poimenujete poljubno, vendar sta običajna imena so bili и tok, kot v uradni pomoči za to funkcijo, kjer:

• so bili – spremenljivka, kjer se akumulira rezultat (njena začetna vrednost je zgoraj navedena seme)
  
• tok – naslednja ponovljena vrednost s seznama Seznam
  

Na primer, poglejmo korake logike naslednje konstrukcije:

=List.Accumulate({3, 2, 5}, 10, (stanje, trenutni) => stanje + trenutni)

To je zadnje zbrano so bili vrednost je naša funkcija List.Accumulate in rezultat kot rezultat:

Če malo fantazirate, lahko s funkcijo List.Accumulate simulirate na primer Excelovo funkcijo CONCATENATE (v Power Queryju se njen analog imenuje Besedilo.Združi) z uporabo izraza:

Ali celo poiščite največjo vrednost (imitacija Excelove funkcije MAX, ki se v Power Query imenuje List.Max):

Vendar pa je glavna značilnost List.Accumulate zmožnost obdelave ne le preprostega besedila ali številskih seznamov kot argumentov, ampak bolj zapletenih predmetov – na primer seznamov-iz-seznamov ali seznamov-iz-zapisov (zdravo, imenik!)

Poglejmo še enkrat konstrukcijo, ki je izvedla zamenjavo v našem problemu:

List.Accumulate(Imenik, [Naslov], (stanje, trenutno) => Text.Replace (stanje, trenutno [Najdi], trenutno [Zamenjaj]) )

Kaj se tukaj pravzaprav dogaja?

1. Kot začetna vrednost (seme) vzamemo prvo nerodno besedilo iz stolpca [Naslov] naša miza: 199034, Sankt Peterburg, str. Beringa, d. 1
2. Nato List.Accumulate ponovi elemente seznama enega za drugim – Priročnik. Vsak element tega seznama je zapis, sestavljen iz para polj »Kaj najti – S čim nadomestiti« ali z drugimi besedami naslednja vrstica v imeniku.
3. Funkcija akumulatorja vstavi v spremenljivko so bili začetna vrednost (prvi naslov 199034, Sankt Peterburg, str. Beringa, d. 1) in na njem izvaja funkcijo akumulatorja – operacijo zamenjave s standardno M-funkcijo Besedilo. Zamenjaj (analogno Excelovi funkciji SUBSTITUTE). Njegova sintaksa je:

   Text.Replace( izvirno besedilo, kaj iščemo, s čim zamenjamo )

   in tukaj imamo:

• so bili je naš umazani naslov, ki leži v so bili (priti tja od seme)
  
• trenutno [Išči] – vrednost polja Najti od naslednjega ponovljenega vnosa na seznamu Imenik, ki leži v spremenljivki tok
• trenutno [Zamenjaj] – vrednost polja Namestnik od naslednjega ponovljenega vnosa na seznamu Imenikležanje v tok

Tako se za vsak naslov vsakič izvede celoten cikel oštevilčenja vseh vrstic v imeniku, pri čemer se besedilo iz polja [Najdi] nadomesti z vrednostjo iz polja [Zamenjaj].

Upam, da ste dobili idejo 🙂

• Množična zamenjava besedila na seznamu z uporabo formul
  
• Regularni izrazi (RegExp) v Power Query