Hromadné nahrazování textu v Power Query pomocí funkce List.Accumulate

Jak rychle a hromadně nahradit text podle referenčního seznamu vzorci – to už jsme vyřešili. Nyní to zkusíme udělat v Power Query.

Jak se často stává provést tento úkol je mnohem jednodušší než vysvětlovat proč jde to, ale zkusme obojí 🙂

Máme tedy dvě „chytré“ dynamické tabulky vytvořené z běžných rozsahů klávesovou zkratkou Ctrl+T nebo tým Domů – Formát jako tabulka (Domů — Formátovat jako tabulku):

Zavolal jsem k prvnímu stolu Data, druhý stůl - Adresářpomocí pole Název tabulky (název tabulky) Karta Stavitel (Design).

Úkol: nahraďte adresy v tabulce Data všechny výskyty ze sloupce Najít Příručka na jejich odpovídající správné protějšky ze sloupce Náhradní. Zbytek textu v buňkách by měl zůstat nedotčen.

Krok 1. Načtěte adresář do Power Query a přeměňte jej na seznam

Po nastavení aktivní buňky na libovolné místo v referenční tabulce klikněte na záložku Data (Datum)nebo na kartě Dotaz na napájení (pokud máte starou verzi Excelu a Power Query jste nainstalovali jako doplněk na samostatnou kartu) na tlačítku Z tabulky/rozsahu (Z tabulky/rozsahu).

Referenční tabulka se načte do editoru dotazů Power Query:

Aby nepřekážel, automaticky přidaný krok upravený typ (Změněný typ) v pravém panelu lze použité kroky bezpečně smazat a ponechat pouze krok Zdroj (Zdroj):

Nyní, abychom mohli provést další transformace a nahrazení, musíme tuto tabulku přeměnit na seznam (seznam).

Lyrická odbočka

• Tabulka je dvourozměrné pole skládající se z několika řádků a sloupců.
• Záznam (záznam) – jednorozměrné pole-řetězec, skládající se například z několika prvků pole s názvy [Jméno = "Masha", Pohlaví = "F", Věk = 25]
  
• Seznam – jednorozměrné pole-sloupec, skládající se například z několika prvků {1, 2, 3, 10, 42} or { "Víra naděje láska" }
  

K vyřešení našeho problému nás bude zajímat především typ Seznam.

Trik je v tom, že položky seznamu v Power Query mohou být nejen banální čísla nebo text, ale také jiné seznamy nebo záznamy. Právě v tak složitém seznamu (seznamu), skládajícím se ze záznamů (záznamů), musíme náš adresář otočit. V syntaktické notaci Power Query (položky v hranatých závorkách, seznamy ve složených závorkách) by to vypadalo takto:

{

    [ Najít = „Sv. Petersburg“, Nahradit = „St. Petersburg”] ,

    [ Najít = „Sv. Petersburg“, Nahradit = „St. Petersburg”] ,

    [ Najít = „Petr“, Nahradit = „Sv. Petersburg”] ,

atd.

}

Taková transformace se provádí pomocí speciální funkce jazyka M zabudovaného do Power Query – Table.ToRecords. Chcete-li ji použít přímo v řádku vzorců, přidejte tuto funkci do kódu kroku tam Zdroj.

To bylo:

Po:

Po přidání funkce Table.ToRecords se vzhled naší tabulky změní – změní se na seznam záznamů. Obsah jednotlivých záznamů lze zobrazit v dolní části podokna zobrazení kliknutím na pozadí buňky vedle libovolného slova Záznam (ale ne jedním slovem!)

Kromě výše uvedeného má smysl přidat ještě jeden tah – do mezipaměti (bufferu) našeho vytvořeného seznamu. To donutí Power Query načíst náš vyhledávací seznam jednou do paměti a nebude jej znovu přepočítávat, když k němu později přistoupíme a nahradíme jej. Chcete-li to provést, zabalte náš vzorec do jiné funkce – List.Buffer:

Takové ukládání do mezipaměti poskytne velmi znatelné zvýšení rychlosti (několikanásobně!) s velkým množstvím počátečních dat, která mají být vymazána.

Tím je příprava příručky dokončena.

Zbývá kliknout Domů – Zavřít a načíst – Zavřít a načíst do… (Domů — Zavřít&Načíst — Zavřít&Načíst do..), Vyberte možnost Stačí vytvořit spojení (Pouze vytvořit připojení) a vrátit se do Excelu.

Krok 2. Načtení datové tabulky

Všechno je tu banální. Stejně jako dříve u referenční knihy se dostaneme na libovolné místo v tabulce, klikneme na záložku Data tlačítko Z tabulky/rozsahu a náš stůl Data dostane do Power Query. Automaticky přidaný krok upravený typ (Změněný typ) můžete také odstranit:

Není třeba s tím dělat žádné speciální přípravné akce a přejdeme k tomu nejdůležitějšímu.

Krok 3. Proveďte výměny pomocí funkce List.Accumulate

Do naší datové tabulky pomocí příkazu přidáme vypočítaný sloupec Přidání sloupce – Vlastní sloupec (Přidat sloupec – Vlastní sloupec): a v okně, které se otevře, zadejte název přidaného sloupce (např. opravená adresa) a naše magická funkce Seznam.Akumulovat:

Zbývá kliknout OK – a dostaneme sloupec s provedenými náhradami:

Všimněte si, že:

• Vzhledem k tomu, že Power Query rozlišuje velká a malá písmena, na předposledním řádku nedošlo k žádné náhradě, protože v adresáři máme „SPb“, nikoli „SPb“.
• Pokud je ve zdrojových datech několik podřetězců k nahrazení najednou (například v 7. řádku je třeba nahradit „S-Pb“ i „Prospekt“), nezpůsobuje to žádné problémy (na rozdíl od nahrazení vzorci z předchozí metoda).
• Pokud ve zdrojovém textu (9. řádek) není co nahradit, nedochází k žádným chybám (opět na rozdíl od nahrazení vzorci).

Rychlost takového požadavku je velmi, velmi slušná. Například pro tabulku počátečních dat o velikosti 5000 řádků byl tento dotaz aktualizován za méně než sekundu (mimochodem bez ukládání do vyrovnávací paměti, asi 3 sekundy!)

Jak funguje funkce List.Accumulate

V zásadě by to mohl být konec (pro mě napsat a pro vás číst) tento článek. Pokud chcete nejen umět, ale také pochopit, jak to funguje „pod pokličkou“, pak se budete muset ponořit trochu hlouběji do králičí nory a vypořádat se s funkcí List.Accumulate, která provedla veškerou hromadnou výměnu pracovat pro nás.

Syntaxe této funkce je:

=List.Accumulate(lest, semínko, akumulátor)

kde

• lest je seznam, jehož prvky iterujeme. 
• semínko – výchozí stav
• akumulátor – funkce, která provede nějakou operaci (matematickou, textovou atd.) na dalším prvku seznamu a akumuluje výsledek zpracování do speciální proměnné.
  

Obecně syntaxe pro zápis funkcí v Power Query vypadá takto:

(argument1, argument2, … argumentN) => některé akce s argumenty

Například sčítací funkce může být reprezentována jako:

(a, b) => a + b

Pro List.Accumulate má tato funkce akumulátoru dva požadované argumenty (mohou být pojmenovány jakkoli, ale obvyklé názvy jsou stát и proud, jako v oficiální nápovědě k této funkci, kde:

• stát – proměnná, kde se shromažďuje výsledek (její počáteční hodnota je výše uvedená semínko)
  
• proud – další iterovaná hodnota ze seznamu lest
  

Podívejme se například na kroky logiky následující konstrukce:

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

Toto je poslední nashromážděný stát hodnota je naše List.Akumulovat funkce a výstupy jako výsledek:

Pokud budete trochu fantazírovat, pak pomocí funkce List.Accumulate můžete nasimulovat např. excelovou funkci CONCATENATE (v Power Query je její analog tzv. Text.Combine) pomocí výrazu:

Nebo dokonce vyhledejte maximální hodnotu (imitace funkce MAX aplikace Excel, která se v Power Query nazývá Seznam.Max):

Hlavním rysem List.Accumulate je však schopnost zpracovávat jako argumenty nejen jednoduché textové nebo číselné seznamy, ale i složitější objekty – například seznamy-ze-seznamů nebo seznamy-ze-záznamů (ahoj, Directory!)

Podívejme se znovu na konstrukci, která provedla výměnu v našem problému:

List.Akumulovat(Adresář, [Adresa], (stav,aktuální) => Text.Nahradit(stav, aktuální[Najít], aktuální[Nahradit]) )

Co se tu vlastně děje?

1. Jako počáteční hodnota (semínko) vezmeme první neohrabaný text ze sloupce [Adresa] náš stůl: 199034, Petrohrad, str. Beringa, d. 1
2. Poté List.Accumulate iteruje prvky seznamu jeden po druhém – Příručka. Každý prvek tohoto seznamu je záznam skládající se z dvojice polí „Co najít – Čím nahradit“ nebo jinými slovy další řádek v adresáři.
3. Funkce akumulátoru vkládá do proměnné stát počáteční hodnota (první adresa 199034, Petrohrad, str. Beringa, d. 1) a provádí na něm funkci akumulátoru – operaci výměny pomocí standardní M-funkce Text.Nahradit (analogicky k funkci SUBSTITUTE v Excelu). Jeho syntaxe je:

   Text.Replace( původní text, co hledáme, čím nahrazujeme )

   a tady máme:

• stát je naše špinavá adresa, která leží v stát (jak se tam dostat semínko)
  
• aktuální[Hledat] – hodnota pole Najít od další iterované položky seznamu Adresář, která leží v proměnné proud
• aktuální[Nahradit] – hodnota pole Náhradní od další iterované položky seznamu Adresářležet v proud

Pro každou adresu se tedy pokaždé spustí celý cyklus výčtu všech řádků v adresáři, přičemž se text z pole [Najít] nahradí hodnotou z pole [Nahradit].

Doufám, že máte nápad 🙂

• Hromadné nahrazení textu v seznamu pomocí vzorců
  
• Regulární výrazy (RegExp) v Power Query