Hromadné nahradenie textu v Power Query pomocou funkcie List.Accumulate

Ako rýchlo a hromadne nahradiť text podľa referenčného zoznamu vzorcami – to sme už vyriešili. Teraz to skúsme urobiť v Power Query.

Ako sa často stáva vykonať táto úloha je oveľa jednoduchšia ako vysvetľovanie prečo ide to, ale skusme oboje 🙂

Máme teda dve „inteligentné“ dynamické tabuľky vytvorené z bežných rozsahov pomocou klávesovej skratky ctrl+T alebo tím Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku):

Zavolal som na prvý stôl dátum, druhý stôl - adresárpomocou poľa Názov tabuľky (názov tabuľky) pútko staviteľ (Dizajn).

Úloha: nahraďte adresy v tabuľke dátum všetky výskyty zo stĺpca Nájsť Príručka na ich zodpovedajúce správne náprotivky zo stĺpca náhradka. Zvyšok textu v bunkách by mal zostať nedotknutý.

Krok 1. Načítajte adresár do Power Query a premeňte ho na zoznam

Po nastavení aktívnej bunky na ľubovoľné miesto v referenčnej tabuľke kliknite na kartu dátum (Dátum)alebo na karte mocenský dotaz (ak máte starú verziu Excelu a Power Query ste nainštalovali ako doplnok na samostatnej karte) na tlačidle Z tabuľky/rozsahu (Z tabuľky/rozsahu).

Referenčná tabuľka sa načíta do editora dotazov Power Query:

Aby neprekážalo, automaticky pridaný krok upravený typ (Zmenený typ) v pravom paneli je možné aplikované kroky bezpečne odstrániť, pričom zostane iba krok zdroj (Zdroj):

Teraz, aby sme mohli vykonať ďalšie transformácie a nahradenia, musíme túto tabuľku zmeniť na zoznam (zoznam).

Lyrická odbočka

• Tabuľka je dvojrozmerné pole pozostávajúce z niekoľkých riadkov a stĺpcov.
• Záznam (záznam) – jednorozmerné pole-reťazec, pozostávajúce z niekoľkých polí-prvkov s názvami, napr [Meno = "Masha", Pohlavie = "F", Vek = 25]
  
• zoznam – jednorozmerné pole-stĺpec, pozostávajúce z niekoľkých prvkov, napr {1, 2, 3, 10, 42} or { "Viera nádej láska" }
  

Na vyriešenie nášho problému nás bude zaujímať predovšetkým typ zoznam.

Trik je v tom, že položky zoznamu v Power Query môžu byť nielen banálne čísla alebo text, ale aj iné zoznamy alebo záznamy. Práve v takom ošemetnom zozname (zozname), pozostávajúcom zo záznamov (záznamov), musíme náš adresár otočiť. V syntaktickej notácii Power Query (položky v hranatých zátvorkách, zoznamy v zložených zátvorkách) by to vyzeralo takto:

{

    [ Nájsť = „Sv. Petersburg“, Nahradiť = „St. Petersburg”] ,

    [ Nájsť = „Sv. Petersburg“, Nahradiť = „St. Petersburg”] ,

    [ Nájsť = „Peter“, Nahradiť = „Sv. Petersburg”] ,

a tak ďalej

}

Takáto transformácia sa vykonáva pomocou špeciálnej funkcie jazyka M zabudovaného do Power Query – Table.ToRecords. Ak ju chcete použiť priamo v riadku vzorcov, pridajte túto funkciu do kódu kroku tam zdroj.

To bolo:

Po:

Po pridaní funkcie Table.ToRecords sa vzhľad našej tabuľky zmení – zmení sa na zoznam záznamov. Obsah jednotlivých záznamov je možné zobraziť v spodnej časti tably zobrazenia kliknutím na pozadie bunky vedľa ľubovoľného slova Záznam (ale nie jedným slovom!)

Okrem vyššie uvedeného má zmysel pridať ešte jeden ťah – ukladať do vyrovnávacej pamäte (buffer) náš vytvorený zoznam. To prinúti Power Query načítať náš vyhľadávací zoznam raz do pamäte a nebude ho znova prepočítavať, keď k nemu neskôr pristúpime, aby sme ho nahradili. Ak to chcete urobiť, zabaľte náš vzorec do inej funkcie – List.Buffer:

Takéto ukladanie do vyrovnávacej pamäte poskytne veľmi výrazné zvýšenie rýchlosti (niekoľkonásobne!) s veľkým množstvom počiatočných údajov, ktoré sa majú vymazať.

Tým je príprava príručky ukončená.

Zostáva kliknúť ďalej Domov – Zavrieť a načítať – Zavrieť a načítať do… (Home — Close&Load — Close&Load to..), vyberte možnosť Stačí vytvoriť spojenie (Iba vytvoriť spojenie) a vráťte sa do Excelu.

Krok 2. Načítanie tabuľky údajov

Všetko je tu banálne. Rovnako ako predtým s referenčnou knihou sa dostaneme na ľubovoľné miesto v tabuľke, kliknite na kartu dátum gombík Z tabuľky/rozsahu a náš stôl dátum dostane do Power Query. Automaticky pridaný krok upravený typ (Zmenený typ) môžete tiež odstrániť:

Netreba s tým robiť žiadne špeciálne prípravné akcie a prejdeme k tomu najdôležitejšiemu.

Krok 3. Vykonajte výmeny pomocou funkcie List.Accumulate

Pridajme vypočítaný stĺpec do našej tabuľky údajov pomocou príkazu Pridanie stĺpca – vlastný stĺpec (Pridať stĺpec – vlastný stĺpec): a v okne, ktoré sa otvorí, zadajte názov pridaného stĺpca (napr. opravená adresa) a naša magická funkcia Zoznam.Akumulovať:

Zostáva kliknúť ďalej OK – a dostaneme stĺpec s vykonanými náhradami:

Poznač si to:

• Keďže Power Query rozlišuje veľké a malé písmená, v predposlednom riadku nedošlo k žiadnej náhrade, pretože v adresári máme „SPb“, nie „SPb“.
• Ak je v zdrojových údajoch nahradených niekoľko podreťazcov naraz (napríklad v 7. riadku je potrebné nahradiť „S-Pb“ aj „Prospekt“), nespôsobuje to žiadne problémy (na rozdiel od nahradenia vzorcami z predchádzajúca metóda).
• Ak v zdrojovom texte nie je čo nahradiť (9. riadok), nevyskytujú sa žiadne chyby (opäť na rozdiel od nahradenia vzorcami).

Rýchlosť takejto požiadavky je veľmi, veľmi slušná. Napríklad pre tabuľku počiatočných údajov s veľkosťou 5000 3 riadkov bol tento dotaz aktualizovaný za menej ako sekundu (mimochodom bez ukladania do vyrovnávacej pamäte, asi XNUMX sekundy!)

Ako funguje funkcia List.Accumulate

V zásade by to mohol byť koniec (pre mňa na písanie a pre vás na čítanie) tohto článku. Ak chcete nielen vedieť, ale aj pochopiť, ako to funguje „pod kapotou“, budete sa musieť ponoriť trochu hlbšie do králičej nory a vysporiadať sa s funkciou List.Accumulate, ktorá urobila všetku hromadnú výmenu pracovať pre nás.

Syntax tejto funkcie je:

=List.Accumulate(zoznam, semienko, akumulátor)

kde

• zoznam je zoznam, ktorého prvky opakujeme. 
• semienko - počiatočný stav
• akumulátor – funkcia, ktorá vykoná nejakú operáciu (matematickú, textovú atď.) na ďalšom prvku zoznamu a výsledok spracovania akumuluje do špeciálnej premennej.
  

Vo všeobecnosti syntax na zapisovanie funkcií v Power Query vyzerá takto:

(argument1, argument2, … argumentN) => niektoré akcie s argumentmi

Napríklad súčtová funkcia môže byť reprezentovaná ako:

(a, b) => a + b

Pre List.Accumulate má táto akumulačná funkcia dva povinné argumenty (možno ich pomenovať akokoľvek, ale bežné názvy sú stať и prúd, ako v oficiálnom pomocníkovi pre túto funkciu, kde:

• stať – premenná, v ktorej sa kumuluje výsledok (jej počiatočná hodnota je tá, ktorá je uvedená vyššie semienko)
  
• prúd – ďalšia iterovaná hodnota zo zoznamu zoznam
  

Pozrime sa napríklad na kroky logiky nasledujúcej konštrukcie:

=List.Accumulate({3, 2, 5}, 10, (stav, prúd) => stav + prúd)

Toto je najnovšie nahromadené stať hodnota je náš zoznam. Akumulovať funkciu a výstupy ako výsledok:

Ak trochu fantazírujete, potom pomocou funkcie List.Accumulate môžete simulovať napríklad funkciu Excel CONCATENATE (v Power Query je jej analóg tzv. Text.Combine) pomocou výrazu:

Alebo dokonca vyhľadajte maximálnu hodnotu (imitácia funkcie MAX v Exceli, ktorá sa v Power Query nazýva Zoznam.Max):

Hlavnou vlastnosťou List.Accumulate je však schopnosť spracovať ako argumenty nielen jednoduché textové alebo číselné zoznamy, ale aj zložitejšie objekty – napríklad zoznamy zo zoznamov alebo zoznamy zo záznamov (ahoj, Directory!)

Pozrime sa znova na konštrukciu, ktorá vykonala výmenu v našom probléme:

List.Accumulate(adresár, [Adresa], (stav,aktuálny) => Text.Nahradiť(stav, aktuálny[Nájsť], aktuálny[Nahradiť]) )

Čo sa tu vlastne deje?

1. Ako počiatočná hodnota (semienko) vezmeme prvý nemotorný text zo stĺpca [Adresa] náš stôl: 199034, Petrohrad, str. Beringa, r. 1
2. Potom List.Accumulate iteruje prvky zoznamu jeden po druhom – Príručka. Každý prvok tohto zoznamu je záznam pozostávajúci z dvojice polí „Čo nájsť – čím nahradiť“ alebo inými slovami, ďalší riadok v adresári.
3. Funkcia akumulátora vkladá do premennej stať počiatočná hodnota (prvá adresa 199034, Petrohrad, str. Beringa, r. 1) a vykonáva na ňom funkciu akumulátora – operáciu výmeny pomocou štandardnej M-funkcie Text.Nahradiť (analogicky k funkcii SUBSTITUTE v Exceli). Jeho syntax je:

   Text.Nahradiť( pôvodný text, čo hľadáme, čím nahrádzame )

   a tu máme:

• stať je naša špinavá adresa, ktorá leží v stať (dostať sa odtiaľ semienko)
  
• aktuálne[Hľadať] – hodnota poľa Nájsť od nasledujúceho opakovaného záznamu zoznamu adresár, ktorá leží v premennej prúd
• aktuálne[Nahradiť] – hodnota poľa náhradka od nasledujúceho opakovaného záznamu zoznamu adresárležať v prúd

Pre každú adresu sa teda zakaždým spustí celý cyklus enumerácie všetkých riadkov v adresári, pričom sa text z poľa [Nájsť] nahradí hodnotou z poľa [Nahradiť].

Dúfam, že máte nápad 🙂

• Hromadné nahradenie textu v zozname pomocou vzorcov
  
• Regulárne výrazy (RegExp) v Power Query