Obsah
Jedno z najsledovanejších videí na mojom kanáli YouTube je video o Flash Fill v programe Microsoft Excel. Podstatou tohto nástroja je, že ak potrebujete nejako transformovať svoje zdrojové údaje, stačí začať písať výsledok, ktorý chcete získať, do susedného stĺpca. Po niekoľkých ručne napísaných bunkách (zvyčajne stačia 2-3) Excel „pochopí“ logiku transformácií, ktoré potrebujete, a automaticky bude pokračovať v tom, čo ste zadali, čím za vás dokončí všetku monotónnu prácu:
Kvintesencia účinnosti. Kúzelné tlačidlo „urob to správne“, ktoré všetci tak milujeme, však?
V skutočnosti existuje analóg takého nástroja v Power Query – tam sa nazýva Stĺpec z príkladov (Stĺpec z príkladov). V skutočnosti ide o malú umelú inteligenciu zabudovanú do Power Query, ktorá sa dokáže rýchlo učiť z vašich údajov a následne ich transformovať. Pozrime sa bližšie na jeho možnosti v niekoľkých praktických scenároch, aby sme pochopili, kde sa nám môže hodiť pri reálnych úlohách.
Príklad 1. Lepenie/strihanie textu
Povedzme, že máme takú „inteligentnú“ tabuľku v Exceli s údajmi o zamestnancoch:
Načítajte ho do Power Query štandardným spôsobom – tlačidlom Z tabuľky/rozsahu pútko dátum (Údaje – z tabuľky/rozsahu).
Predpokladajme, že musíme pridať stĺpec s priezviskami a iniciálami pre každého zamestnanca (Ivanov SV pre prvého zamestnanca atď.). Na vyriešenie tohto problému môžete použiť jednu z dvoch metód:
- kliknite pravým tlačidlom myši na hlavičku stĺpca so zdrojovými údajmi a vyberte príkaz Pridajte stĺpec z príkladov (Pridať stĺpec z príkladov);
- vyberte jeden alebo viac stĺpcov s údajmi a na karte Pridanie stĺpca vybrať tím Stĺpec z príkladov. Tu v rozbaľovacom zozname môžete určiť, či je potrebné analyzovať všetky alebo iba vybrané stĺpce.
Potom je už všetko jednoduché – do stĺpca, ktorý sa zobrazí napravo, začneme zadávať príklady požadovaných výsledkov a umelá inteligencia zabudovaná do Power Query sa snaží pochopiť našu transformačnú logiku a pokračovať ďalej sama:
Mimochodom, správne možnosti môžete zadať do ľubovoľných buniek tohto stĺpca, tj nie nevyhnutne zhora nadol a v riadku. Pomocou začiarkavacích políčok v záhlaví môžete tiež jednoducho pridať alebo odstrániť stĺpce z analýzy neskôr.
Venujte pozornosť vzorcu v hornej časti okna – toto vytvára inteligentné Power Query na dosiahnutie požadovaných výsledkov. To je mimochodom zásadný rozdiel medzi týmto nástrojom a Okamžitá náplň v Exceli. Okamžité plnenie funguje ako „čierna skrinka“ – neukazuje nám logiku transformácií, ale jednoducho dáva hotové výsledky a my ich považujeme za samozrejmosť. Tu je všetko transparentné a vždy môžete úplne jasne pochopiť, čo sa presne s údajmi deje.
Ak vidíte, že Power Query „chytil nápad“, môžete bezpečne stlačiť tlačidlo OK alebo klávesová skratka ctrl+vstúpiť – vytvorí sa vlastný stĺpec so vzorcom, ktorý vymyslel Power Query. Mimochodom, dá sa neskôr ľahko upraviť ako bežný ručne vytvorený stĺpec (príkazom Pridanie stĺpca – vlastný stĺpec) kliknutím na ikonu ozubeného kolieska napravo od názvu kroku:
Príklad 2: Prípad ako vo vetách
Ak kliknete pravým tlačidlom myši na hlavičku stĺpca s textom a vyberiete príkaz Premena (Transformácia), potom môžete vidieť tri príkazy zodpovedné za zmenu registra:
Pohodlné a cool, ale mne osobne napríklad v tomto zozname vždy chýbala ešte jedna možnosť – prípad ako vo vetách, keď sa veľké písmeno (veľké) nestane prvým písmenom v každom slove, ale iba prvým písmenom v bunke a zvyšok textu, keď sa toto zobrazí malými (malými) písmenami.
Táto chýbajúca funkcia je ľahko implementovateľná pomocou umelej inteligencie Stĺpce z príkladov – stačí zadať niekoľko možností pre Power Query, aby ste pokračovali v rovnakom duchu:
Ako vzorec tu Power Query používa množstvo funkcií Text.Upper и Text.Nižšie, prevod textu na veľké a malé písmená a funkcie Text.Start и Text.Stred – analógy funkcií Excelu LEFT a PSTR, schopné extrahovať podreťazec z textu zľava a zo stredu.
Príklad 3. Permutácia slov
Niekedy pri spracovaní prijatých údajov je potrebné zmeniť usporiadanie slov v bunkách v danej sekvencii. Samozrejme, môžete stĺpec rozdeliť na samostatné stĺpce slov oddeľovačom a potom ho prilepiť späť v určenom poradí (nezabudnite pridať medzery), ale pomocou nástroja Stĺpec z príkladov všetko bude oveľa jednoduchšie:
Príklad 4: Iba čísla
Ďalšou veľmi dôležitou úlohou je vytiahnuť z obsahu bunky iba čísla (čísla). Rovnako ako predtým, po načítaní údajov do Power Query prejdite na kartu Pridanie stĺpca – Stĺpec z príkladov a ručne vyplňte niekoľko buniek, aby program pochopil, čo presne chceme získať:
Bingo!
Opäť sa oplatí pozrieť sa na hornú časť okna, aby ste sa uistili, že Query vygeneroval vzorec správne – v tomto prípade obsahuje funkciu Text. Vyberte, ktorý, ako už asi tušíte, vytiahne dané znaky zo zdrojového textu podľa zoznamu. Následne je tento zoznam, samozrejme, možné v prípade potreby jednoducho upravovať vo vzorcovom riadku.
Príklad 5: Len text
Podobne ako v predchádzajúcom príklade môžete vytiahnuť a naopak – iba text, vymazanie všetkých čísel, interpunkčných znamienok atď.
V tomto prípade sa používa významovo už opačná funkcia – Text.Remove, ktorá odstráni znaky z pôvodného reťazca podľa daného zoznamu.
Príklad 6: Extrahovanie údajov z alfanumerickej kaše
Power Query môže pomôcť aj v zložitejších prípadoch, keď potrebujete z alfanumerickej kaše v bunke vytiahnuť užitočné informácie, napríklad získať číslo účtu z popisu účelu platby na výpise z účtu:
Upozorňujeme, že vzorec konverzie generovaný Power Query môže byť pomerne zložitý:
Pre uľahčenie čítania a pochopenia ho možno previesť do oveľa rozumnejšej podoby pomocou bezplatnej online služby. Formátovač Power Query:
Veľmi šikovná vec – úcta k tvorcom!
Príklad 7: Konverzia dátumov
Nástroj Stĺpec z príkladov možno použiť aj na stĺpce dátumu alebo dátumu a času. Keď zadáte prvé číslice dátumu, Power Query užitočne zobrazí zoznam všetkých možných možností prevodu:
Pôvodný dátum teda môžete jednoducho previesť do akéhokoľvek exotického formátu, napríklad „rok-mesiac-deň“:
Príklad 8: Kategorizácia
Ak použijeme nástroj Stĺpec z príkladov do stĺpca s číselnými údajmi, funguje to inak. Predpokladajme, že máme výsledky testov zamestnancov načítané do Power Query (podmienené skóre v rozsahu 0-100) a používame nasledujúcu podmienenú gradáciu:
- Masters – tí, ktorí dosiahli viac ako 90
- Experti – skóre od 70 do 90
- Používatelia – od 30 do 70
- Začiatočníci – tí, ktorí dosiahli menej ako 30 bodov
Ak do zoznamu pridáme stĺpec z príkladov a začneme tieto gradácie usporadúvať ručne, tak veľmi skoro Power Query preberie náš nápad a pridá stĺpec so vzorcom, kde sa operátori vnoria do seba if bude implementovaná logika, veľmi podobná tomu, čo potrebujeme:
Opäť nemôžete situáciu dotiahnuť do konca, ale kliknúť ďalej OK a potom opravte prahové hodnoty už vo vzorci – je to rýchlejšie takto:
Závery
Určite nástroj Stĺpec z príkladov nie je „čarovná pilulka“ a skôr či neskôr sa v dátach vyskytnú neštandardné situácie alebo obzvlášť zanedbané prípady „kolektívnej farmy“, kedy Power Query zlyhá a nebude vedieť vypracovať to, čo chceme pre nás správne. Ako pomocný nástroj je však veľmi dobrý. Navyše, štúdiom vzorcov, ktoré vygeneroval, si môžete rozšíriť znalosti o funkciách jazyka M, čo sa vám v budúcnosti bude vždy hodiť.
- Analýza textu s regulárnymi výrazmi (RegExp) v Power Query
- Fuzzy textové vyhľadávanie v Power Query
- Flash Vyplňte v programe Microsoft Excel