Obsah
Ak sa aspoň trochu vyznáte v regulárnych výrazoch, tak im nemusíte robiť reklamu. Ak nie ste celkom v predmete, regulárne výrazy (Regular Expressions = RegExp = „regexps“ = „regulars“) je jazyk, v ktorom sa pomocou špeciálnych znakov a pravidiel hľadajú potrebné podreťazce v texte, extrahujú sa alebo nahradený iným textom. Je to veľmi výkonný a krásny nástroj, ktorý je rádovo lepší ako všetky ostatné spôsoby práce s textom.
Už som podrobne a s kopou príkladov zo života popísal, ako môžete do Excelu pridať podporu regulárnych výrazov pomocou jednoduchých makier – ak ste tento článok nečítali, vrelo odporúčam, aby ste si ho pred pokračovaním prečítali. Objavíte veľa nového, garantujem 🙂
Otázka však zostáva otvorená – ako pridať možnosť používať regulárne výrazy v Power Query? Power Query je samozrejme dobrý aj sám o sebe a s textom toho dokáže veľa (rezať, lepiť, čistiť atď.), no ak by ste ho skrížili silou regulárnych výrazov, bola by to proste bomba.
Bohužiaľ, v Power Query nie sú žiadne vstavané funkcie pre prácu s RegExps a oficiálna pomoc a technická podpora spoločnosti Microsoft odpovedajú na túto otázku záporne. Existuje však spôsob, ako toto obmedzenie obísť 🙂
Podstata metódy
Hlavná myšlienka je jednoduchá na hanbu.
V zozname vstavaných schopností Power Query je funkcia Webstránka. Popis tejto funkcie na oficiálnej stránke pomoci spoločnosti Microsoft je mimoriadne stručný:
V preklade by to bolo: „Vráti obsah dokumentu HTML rozdelený na jednotlivé štruktúry, ako aj reprezentáciu celého dokumentu a jeho tela po odstránení značiek.“ Tak taký popis, úprimne povedané.
Zvyčajne sa táto funkcia používa pri importe údajov z webu a automaticky sa nahrádza napríklad pri výbere na karte dátum povel Z internetu (Údaje – z webu). Funkcii dáme ako argument webovú stránku a ona nám vráti svoj obsah vo forme tabuliek, pričom predtým vymazala všetky značky.
Čo sa v nápovede nepíše je, že okrem značkovacieho jazyka HTML funkcie Webstránka podporuje skripty JavaScript, ktorý je teraz všadeprítomný na webových stránkach na internete. A JavaScript zase vždy vedel pracovať s regulárnymi výrazmi a má vstavané funkcie pre RegExps! Ak teda chceme implementovať regulárne výrazy v Power Query, budeme musieť poskytnúť funkcie Web.Page ako argument pre malý JavaScriptový program, ktorý urobí všetku prácu pre Power Query.
Ako to vyzerá v čistom JavaScripte
Na internete je množstvo podrobných návodov na prácu s regulárnymi výrazmi v JavaScripte (napríklad jeden, dva).
V skratke a zjednodušene bude kód JavaScript vyzerať takto:
Tu:
- var str = 'Zaplaťte účty 123 a 789 za klobásu'; – vytvoriť premennú str a priradíme mu zdrojový text, ktorý budeme analyzovať.
- var vzor = /d+/gi; – vytvorte regulárny výraz a vložte ho do premennej vzor.
Výraz sa začína lomkou (/).
Samotný výraz tu napríklad je d+ znamená ľubovoľnú postupnosť číslic.
Prostredníctvom zlomku za výrazom existujú ďalšie parametre vyhľadávania (modifikátory) – možno ich zadať v ľubovoľnom poradí:
- g – znamená globálne vyhľadávanie, teda po nájdení zhody by ste nemali prestať, ale pokračovať v hľadaní až do konca textu. Ak tento modifikátor nie je nastavený, náš skript vráti iba prvú zhodu (123)
- i – vyhľadávanie bez ohľadu na veľkosť písmen
- m – viacriadkové vyhľadávanie (používa sa, keď je zdrojový text rozdelený do niekoľkých riadkov)
- var vysledok = str.match(vzor).join(';'); – vykonajte vyhľadávanie v zdrojovom texte (str) daným regulárnym výrazom (vzor) a výsledky vložte do premennej následok, pričom ich zreťazíte bodkočiarkou pomocou príkazu spojiť
- dokument.zapis(vysledok); – zobrazenie obsahu premennej result
Všimnite si tiež, že textové reťazce (okrem regulárnych výrazov) v JavaScripte sú uzavreté v apostrofoch, nie v úvodzovkách, ako je to v Power Query alebo VBA.
Na výstupe nám tento skript poskytne všetky čísla nájdené v zdrojovom texte:
123, 789
Krátky kurz JavaScriptu sa skončil, ďakujeme všetkým. Dúfam, že chápete logiku 🙂
Zostáva preniesť túto konštrukciu do Power Query.
Funkcia vyhľadávania a extrakcie textu pomocou regulárneho výrazu v Power Query
Robíme nasledovné:
1. Otvorte Excel a vytvorte nový prázdny Power Query na karte Údaje – Získať údaje / Vytvoriť požiadavku – Z iných zdrojov – Vyprázdniť požiadavku (Údaje — Získať údaje / Nový dopyt — Z iných zdrojov — Prázdny dopyt). Ak máte starú verziu Excelu 2010-2013 a Power Query nemáte vstavanú, ale bola nainštalovaná ako samostatný doplnok, tak toto všetko bude na karte mocenský dotazA nie dátum.
2. V prázdnom okne editora dotazov, ktoré sa otvorí, v pravom paneli okamžite zadajte názov našej budúcej funkcie (napr. fxRegExpExtract)
3. Poďme na kartu Zobraziť – Pokročilý editor (Zobraziť — Pokročilý editor), vymažeme celý M-kód prázdnej požiadavky a vložíme tam kód našej superfunkcie:
Pozor na ruky:
V prvom riadku hovoríme, že naša funkcia bude mať tri textové argumenty: txt – pôvodný text, ktorý sa analyzuje, regex - vzor regulárneho výrazu, DELI — oddeľovací znak pre zobrazenie výsledkov.
Ďalej zavoláme funkciu Webstránka, tvoriaci kód JavaScript opísaný vyššie vo svojom argumente. Vložíme a nahradíme naše variabilné argumenty do kódu.
Fragment:
[Údaje]{0}[Deti]{0}[Deti]{1}[Text]{0}
… je potrebný na „prepadnutie“ do tabuľky s výsledkami, ktoré potrebujeme. Ide o to, že funkcia Webstránka v dôsledku toho vytvára niekoľko vnorených tabuliek, ktoré opakujú štruktúru webovej stránky. Bez tohto kúsku M-kódu by naša funkcia vypísala toto:
... a museli by sme kliknúť na slovo niekoľkokrát Tabuľka, ktorý postupne „prepadáva“ do podriadených vnorených tabuliek v stĺpcoch Deti:
Namiesto celej tejto citácie v kóde našej funkcie okamžite uvedieme, ktorá vnorená tabuľka a stĺpec (text) potrebujeme.
Tu sú v skutočnosti všetky tajomstvá. Zostáva stlačiť tlačidlo úprava v okne pokročilý editor, kde sme vložili náš kód a môžete prejsť k tomu najchutnejšiemu – vyskúšajte našu funkciu v práci.
Tu je niekoľko príkladov semien.
Príklad 1. Získanie čísla účtu a dátumu z popisu platby
Máme výpis z účtu s popisom (účelom) platieb, kde je potrebné vytiahnuť čísla a dátumy uhradených faktúr do samostatných stĺpcov:
Tabuľku načítame do Power Query štandardným spôsobom Údaje – z tabuľky/rozsahu (Údaje – od Tschopný/Ranjel).
Potom pridáme vypočítaný stĺpec s našou funkciou via Pridať stĺpec – Zavolajte vlastnú funkciu (Pridať stĺpec – vyvolať vlastnú funkciu) a zadajte jeho argumenty:
Ako regulárny výraz (argument regex) šablóna, ktorú používame:
(d{3,5}|d{2}.d{2}.d{4})
… preložené do ľudskej reči znamená:
čísla od 3 do 5 číslic (čísla účtov)
or
fragmenty v tvare „2-bitové číslo – bod – 2-bitové číslo – bod – 4-bitové číslo“, teda dátumy v tvare DD.MM.RRRR.
Ako oddeľovací znak (argument DELI) zadajte bodkočiarku.
Po kliknutí na OK naša magická funkcia analyzuje všetky počiatočné údaje podľa nášho regulárneho výrazu a vytvorí nám stĺpec s nájdenými číslami a dátumami faktúr:
Zostáva ho oddeliť bodkočiarkou pomocou príkazu Domov — Rozdeliť stĺpec — Podľa oddeľovača (Domov — Rozdeliť stĺpec — Podľa oddeľovača) a dostaneme, čo sme chceli:
Krása!
Príklad 2: Extrahujte e-mailové adresy z textu
Predpokladajme, že ako počiatočné údaje máme nasledujúcu tabuľku:
… odkiaľ potrebujeme vytiahnuť tam nájdené emailové adresy (pre prehľadnosť som ich v texte zvýraznil červenou farbou).
Rovnako ako v predchádzajúcom príklade načítame tabuľku do Power Query štandardným spôsobom cez Údaje – z tabuľky/rozsahu (Údaje – od Tschopný/Ranjel).
Potom pridáme vypočítaný stĺpec s našou funkciou via Pridať stĺpec – Zavolajte vlastnú funkciu (Pridať stĺpec – vyvolať vlastnú funkciu) a zadajte jeho argumenty:
Analýza e-mailových adries je náročnejšia úloha a na jej vyriešenie existuje množstvo regulárnych výrazov rôzneho stupňa nočnej mory. Použil som jednu z jednoduchých možností – nie ideálnu, ale vo väčšine prípadov celkom fungujúcu:
[w|.|-]*@w*.[w|.]*
Ako oddeľovač (DELI) môžete zadať bodkočiarku a medzeru.
Kliknite na OK a dostaneme stĺpec s e-mailovými adresami extrahovanými z pôvodného textu „kaša“:
Mágia!
PS
Ako sa hovorí: "Neexistuje taká dobrá vec, ktorá by sa nedala urobiť ešte lepším." Power Query je cool sám o sebe a v kombinácii s regulárnymi výrazmi nám dáva úplne nereálny výkon a flexibilitu pri spracovaní akýchkoľvek textových údajov. Dúfam, že Microsoft jedného dňa pridá podporu RegExp v aktualizáciách Power Query a Power BI a všetky vyššie uvedené tance s tamburínou sa stanú minulosťou. No zatiaľ áno.
Chcem tiež dodať, že je vhodné hrať sa s regulárnymi výrazmi na stránke https://regexr.com/ – priamo v online editore. Tam v sekcii Komunitné vzory Existuje obrovské množstvo pripravených pravidelných sezón na všetky príležitosti. Experimentujte – celá sila regulárnych výrazov je teraz k vašim službám v Power Query!
- Čo sú regulárne výrazy (RegExp) a ako ich používať v Exceli
- Fuzzy textové vyhľadávanie v Power Query
- Zostavovanie tabuliek z rôznych súborov pomocou Power Query