Obsah
Jedno z nejsledovanějších videí na mém kanálu YouTube je video o Flash Fill v aplikaci Microsoft Excel. Podstatou tohoto nástroje je, že pokud potřebujete nějak transformovat zdrojová data, pak stačí začít psát výsledek, který chcete získat, do sousedního sloupce. Po několika ručně zadaných buňkách (obvykle stačí 2-3) Excel „pochopí“ logiku transformací, které potřebujete, a automaticky pokračuje v tom, co jste napsali, čímž dokončí veškerou monotónní práci za vás:
Kvintesence účinnosti. Kouzelné tlačítko „udělej to správně“, které všichni tak milujeme, že?
Ve skutečnosti existuje obdoba takového nástroje v Power Query – tam se nazývá Sloupec z příkladů (sloupec z příkladů). Ve skutečnosti se jedná o malou umělou inteligenci zabudovanou do Power Query, která se dokáže rychle učit z vašich dat a následně je transformovat. Podívejme se blíže na jeho schopnosti v několika praktických scénářích, abychom pochopili, kde se nám může hodit při skutečných úkolech.
Příklad 1. Lepení/stříhání textu
Řekněme, že máme v Excelu takovou „chytrou“ tabulku s údaji o zaměstnancích:
Nahrajte jej do Power Query standardním způsobem – tlačítkem Z tabulky/rozsahu Karta Data (Data – z tabulky/rozsahu).
Předpokládejme, že potřebujeme přidat sloupec s příjmením a iniciálami pro každého zaměstnance (Ivanov SV pro prvního zaměstnance atd.). Chcete-li tento problém vyřešit, můžete použít jednu ze dvou metod:
- klikněte pravým tlačítkem na záhlaví sloupce se zdrojovými daty a vyberte příkaz Přidejte sloupec z příkladů (Přidat sloupec z příkladů);
- vyberte jeden nebo více sloupců s daty a na kartě Přidání sloupce vybrat tým Sloupec z příkladů. Zde v rozevíracím seznamu můžete určit, zda je třeba analyzovat všechny nebo pouze vybrané sloupce.
Pak je vše jednoduché – do sloupce, který se objeví vpravo, začneme zadávat příklady požadovaných výsledků a umělá inteligence zabudovaná do Power Query se snaží pochopit naši transformační logiku a pokračovat dále sama:
Mimochodem, správné možnosti můžete zadat do libovolných buněk tohoto sloupce, tedy ne nutně shora dolů a na řádek. Sloupce můžete také později snadno přidat nebo odebrat z analýzy pomocí zaškrtávacích políček v záhlaví.
Věnujte pozornost vzorci v horní části okna – to je to, co chytrý Power Query vytváří, aby získal výsledky, které potřebujeme. To je mimochodem zásadní rozdíl mezi tímto nástrojem a Okamžité plnění v Excelu. Okamžité plnění funguje jako „černá skříňka“ – neukazují nám logiku transformací, ale jednoduše dávají hotové výsledky a my je bereme jako samozřejmost. Zde je vše transparentní a vždy naprosto jasně pochopíte, co se přesně s daty děje.
Pokud vidíte, že Power Query „chytil nápad“, můžete bezpečně stisknout tlačítko OK nebo klávesovou zkratkou Ctrl+vstoupit – vytvoří se vlastní sloupec se vzorcem vynalezeným Power Query. Mimochodem, lze jej později snadno upravit jako běžný ručně vytvořený sloupec (pomocí příkazu Přidání sloupce – Vlastní sloupec) kliknutím na ikonu ozubeného kola vpravo od názvu kroku:
Příklad 2: Případ jako ve větách
Pokud kliknete pravým tlačítkem na záhlaví sloupce s textem a vyberete příkaz Proměna (Přeměnit), pak můžete vidět tři příkazy odpovědné za změnu registru:
Pohodlné a cool, ale třeba mně osobně v tomto seznamu vždy chyběla ještě jedna možnost – případ jako ve větách, kdy se velké (velké) nestává prvním písmenem v každém slově, ale pouze prvním písmenem v buňce a zbytek textu, když se toto zobrazí malými (malými) písmeny.
Tuto chybějící funkci lze snadno implementovat pomocí umělé inteligence Sloupce z příkladů – stačí zadat několik možností, aby Power Query pokračoval ve stejném duchu:
Jako vzorec zde Power Query používá spoustu funkcí Text.Upper и Text.Nižší, převod textu na velká a malá písmena a funkce Text.Start и Text.Střed – analogy funkcí Excelu LEFT a PSTR, schopné extrahovat podřetězec z textu zleva a ze středu.
Příklad 3. Permutace slov
Někdy se při zpracování přijatých dat stává nutností přeskupit slova v buňkách v dané sekvenci. Samozřejmě můžete sloupec rozdělit na samostatné sloupce slov oddělovačem a poté jej v určeném pořadí slepit zpět (nezapomeňte přidat mezery), ale pomocí nástroje Sloupec z příkladů vše bude mnohem jednodušší:
Příklad 4: Pouze čísla
Dalším velmi zásadním úkolem je vytáhnout z obsahu buňky pouze čísla (čísla). Stejně jako dříve po načtení dat do Power Query přejděte na kartu Přidání sloupce – Sloupec z příkladů a ručně vyplňte několik buněk, aby program pochopil, co přesně chceme získat:
Bingo!
Opět se vyplatí podívat se na horní část okna, abyste se ujistili, že Query vygeneroval vzorec správně – v tomto případě obsahuje funkci Text. Vybrat, který, jak asi tušíte, vytahuje dané znaky ze zdrojového textu podle seznamu. Následně lze tento seznam samozřejmě v případě potřeby snadno upravovat ve vzorcovém řádku.
Příklad 5: Pouze text
Podobně jako v předchozím příkladu lze vytáhnout a naopak – pouze text, smazání všech čísel, interpunkčních znamének atd.
V tomto případě je použita významově již opačná funkce – Text.Remove, která odebere znaky z původního řetězce podle daného seznamu.
Příklad 6: Extrahování dat z alfanumerické kaše
Power Query může pomoci i ve složitějších případech, kdy potřebujete z alfanumerické kaše v buňce vydolovat užitečné informace, například získat číslo účtu z popisu účelu platby na výpisu z účtu:
Všimněte si, že převodní vzorec generovaný Power Query může být poměrně složitý:
Pro snadné čtení a porozumění jej lze převést do mnohem rozumnější podoby pomocí bezplatné online služby. Formátovač Power Query:
Velmi šikovná věc – respekt tvůrcům!
Příklad 7: Převod dat
Nástroj Sloupec z příkladů lze použít také na sloupce data nebo datetime. Když zadáte první číslice data, Power Query užitečně zobrazí seznam všech možných možností převodu:
Původní datum tedy můžete snadno převést do libovolného exotického formátu, například „rok-měsíc-den“:
Příklad 8: Kategorizace
Pokud použijeme nástroj Sloupec z příkladů do sloupce s číselnými údaji, to funguje jinak. Předpokládejme, že máme výsledky testů zaměstnanců načtené do Power Query (podmíněné skóre v rozsahu 0–100) a používáme následující podmíněnou gradaci:
- Masters – ti, kteří dosáhli více než 90 bodů
- Experti – skóre od 70 do 90
- Uživatelé – od 30 do 70
- Začátečníci – ti, kteří dosáhli méně než 30 bodů
Pokud do seznamu přidáme sloupec z příkladů a začneme tyto gradace uspořádat ručně, pak velmi brzy Power Query vezme náš nápad a přidá sloupec se vzorcem, kde se operátory vnořují do sebe if bude implementována logika, velmi podobná tomu, co potřebujeme:
Opět nelze situaci dotáhnout do konce, ale kliknout dál OK a poté opravte prahové hodnoty již ve vzorci – je to rychlejší takto:
Závěry
Určitě nástroj Sloupec z příkladů není „kouzelná pilulka“ a dříve nebo později dojde v datech k nestandardním situacím nebo zvláště opomíjeným případům „kolektivní farmy“, kdy Power Query selže a nebude schopen vypracovat to, co chceme pro nás správně. Jako pomocný nástroj je však velmi dobrý. Studiem jím vygenerovaných vzorců si navíc můžete rozšířit znalosti o funkcích jazyka M, což se v budoucnu bude vždy hodit.
- Analýza textu s regulárními výrazy (RegExp) v Power Query
- Fuzzy textové vyhledávání v Power Query
- Flash Vyplňte v aplikaci Microsoft Excel