Obsah
Pokud se alespoň trochu vyznáte v regulárních výrazech, pak je nemusíte inzerovat. Pokud nejste úplně v předmětu, pak regulární výrazy (Regular Expressions = RegExp = “regexps” = “regulars”) je jazyk, kde se pomocí speciálních znaků a pravidel hledají potřebné podřetězce v textu, extrahují se nebo nahrazen jiným textem . Jedná se o velmi výkonný a krásný nástroj, řádově lepší než všechny ostatní způsoby práce s textem.
Jak lze do Excelu přidat podporu regulárních výrazů pomocí jednoduchých maker jsem již podrobně a s hromadou příkladů ze života popsal – pokud jste tento článek nečetli, vřele doporučuji si jej přečíst, než budete pokračovat. Objevíte spoustu nového, to garantuji 🙂
Otázka však zůstává otevřená – jak přidat možnost používat regulární výrazy v Power Query? Power Query je samozřejmě dobrý sám o sobě a umí toho s textem opravdu hodně (řezání, lepení, čištění atd.), ale kdybyste to zkřížili silou regulárních výrazů, byla by to prostě bomba.
Bohužel v Power Query nejsou žádné vestavěné funkce pro práci s RegExps a oficiální nápověda a technická podpora Microsoftu na tuto otázku odpovídají záporně. Nicméně existuje způsob, jak toto omezení obejít 🙂
Podstata metody
Hlavní myšlenka je jednoduchá na ostudu.
V seznamu vestavěných schopností Power Query je funkce Webová stránka. Popis této funkce na oficiálním webu nápovědy společnosti Microsoft je velmi stručný:
V překladu by to bylo: „Vrátí obsah dokumentu HTML rozdělený do struktur jednotlivých složek a také reprezentaci celého dokumentu a jeho těla po odstranění značek.“ Tak-tak popis, upřímně.
Obvykle se tato funkce používá při importu dat z webu a je automaticky nahrazena například při výběru na kartě Data Příkaz Z internetu (Data – z webu). Funkci dáme jako argument webovou stránku a ona nám vrátí svůj obsah ve formě tabulek poté, co předtím vyčistila všechny značky.
Co se v nápovědě nepíše je, že kromě značkovacího jazyka HTML funkce Webová stránka podporuje JavaScript skripty, který je nyní všudypřítomný na webových stránkách na internetu. A JavaScript zase vždy uměl pracovat s regulárními výrazy a má vestavěné funkce pro RegExps! Abychom tedy mohli implementovat regulární výrazy v Power Query, budeme muset poskytnout funkce Web.Page jako argument pro malý JavaScriptový program, který udělá veškerou práci pro Power Query.
Jak to vypadá v čistém JavaScriptu
Na internetu je spousta podrobných návodů na práci s regulárními výrazy v JavaScriptu (například jedna, dvě).
Stručně a zjednodušeně bude kód JavaScriptu vypadat takto:
Zde:
- var str = 'Zaplaťte účty 123 a 789 za klobásu'; – vytvořit proměnnou str a přiřadit mu zdrojový text, který budeme analyzovat.
- var vzor = /d+/gi; – vytvořte regulární výraz a vložte jej do proměnné vzor.
Výraz začíná lomítkem (/).
Samotný výraz zde například je d+ znamená libovolnou posloupnost číslic.
Prostřednictvím zlomku za výrazem existují další parametry vyhledávání (modifikátory) – lze je zadat v libovolném pořadí:
- g – znamená globální hledání, tj. po nalezení shody byste neměli přestat, ale pokračovat v hledání až do konce textu. Pokud tento modifikátor není nastaven, pak náš skript vrátí pouze první shodu (123)
- i – vyhledávání bez ohledu na velikost písmen
- m – víceřádkové vyhledávání (používá se, když je zdrojový text rozdělen do několika řádků)
- var vysledek = str.match(vzor).join(';'); – proveďte vyhledávání ve zdrojovém textu (str) daným regulárním výrazem (vzor) a výsledky vložte do proměnné následek, zřetězením středníkem pomocí příkazu spojit
- dokument.zapis(vysledek); – zobrazí obsah proměnné result
Všimněte si také, že textové řetězce (kromě regulárních výrazů) v JavaScriptu jsou uzavřeny v apostrofech, nikoli v uvozovkách, jako je tomu v Power Query nebo VBA.
Na výstupu nám tento skript poskytne všechna čísla nalezená ve zdrojovém textu:
123, 789
Krátký kurz JavaScriptu skončil, děkujeme všem. Snad chápete logiku 🙂
Zbývá přenést tuto konstrukci do Power Query.
Funkce vyhledávání a extrahování textu regulárním výrazem v Power Query
Provádíme následující:
1. Otevřete Excel a na kartě vytvořte nový prázdný Power Query Data – Získat data / Vytvořit požadavek – Z jiných zdrojů – Prázdný požadavek (Data — Získat data / Nový dotaz — Z jiných zdrojů — Prázdný dotaz). Pokud máte starou verzi Excelu 2010-2013 a Power Query nemáte vestavěnou, ale byla nainstalována jako samostatný doplněk, pak to vše bude na kartě Dotaz na napájeníA ne Data.
2. V prázdném okně editoru dotazů, které se otevře, v pravém panelu okamžitě zadejte název naší budoucí funkce (např. fxRegExpExtract)
3. Pojďme na kartu Zobrazit – Pokročilý editor (Zobrazit — Pokročilý editor), vymažeme celý M-kód prázdného požadavku a vložíme tam kód naší superfunkce:
Pozor na ruce:
V prvním řádku říkáme, že naše funkce bude mít tři textové argumenty: txt – původní analyzovaný text, regex – vzor regulárního výrazu, vymezit — oddělovací znak pro zobrazení výsledků.
Dále zavoláme funkci Webová stránka, tvořící kód JavaScript popsaný výše ve svém argumentu. Do kódu vložíme a dosadíme naše proměnné argumenty.
Fragment:
[Data]{0}[Děti]{0}[Děti]{1}[Text]{0}
… je potřeba, abychom „propadli“ do tabulky s výsledky, které potřebujeme. Jde o to, že funkce Webová stránka výsledkem je několik vnořených tabulek, které opakují strukturu webové stránky. Bez tohoto kousku M-kódu by naše funkce vydala toto:
… a museli bychom na slovo kliknout několikrát Tabulka, postupně „propadávat“ do podřízených vnořených tabulek ve sloupcích Děti:
Místo celé této citace v kódu naší funkce okamžitě uvedeme, která vnořená tabulka a sloupec (Text) potřebujeme.
Zde jsou ve skutečnosti všechna tajemství. Zbývá stisknout tlačítko úprava v okně pokročilý editor, kam jsme vložili náš kód, a můžete přejít k tomu nejchutnějšímu – vyzkoušejte naši funkci v práci.
Zde je několik příkladů semen.
Příklad 1. Získání čísla účtu a data z popisu platby
Máme výpis z účtu s popisem (účelem) plateb, kde je potřeba vytáhnout čísla a data uhrazených faktur do samostatných sloupců:
Tabulku načteme do Power Query standardním způsobem Data – z tabulky/rozsahu (Údaje – od Tschopný/Randěl).
Poté přidáme vypočítaný sloupec s naší funkcí přes Přidat sloupec – Volání uživatelské funkce (Přidat sloupec – vyvolat vlastní funkci) a zadejte jeho argumenty:
Jako regulární výraz (argument regex) šablona, kterou používáme:
(d{3,5}|d{2}.d{2}.d{4})
… přeloženo do lidské řeči znamená:
čísla od 3 do 5 číslic (čísla účtů)
or
fragmenty ve tvaru „2bitové číslo – bod – 2bitové číslo – bod – 4bitové číslo“, tedy data ve tvaru DD.MM.RRRR.
Jako oddělovací znak (argument vymezit) zadejte středník.
Po kliknutí na tlačítko OK naše magická funkce analyzuje všechna počáteční data podle našeho regulárního výrazu a vytvoří nám sloupec s nalezenými čísly a daty faktur:
Zbývá jej oddělit středníkem pomocí příkazu Domů — Rozdělit sloupec — Podle oddělovače (Domů — Rozdělit sloupec — Podle oddělovače) a dostaneme, co jsme chtěli:
Krása!
Příklad 2: Extrahujte e-mailové adresy z textu
Předpokládejme, že jako počáteční data máme následující tabulku:
… odkud potřebujeme vytáhnout nalezené emailové adresy (pro přehlednost jsem je v textu zvýraznil červeně).
Stejně jako v předchozím příkladu načteme tabulku do Power Query standardním způsobem přes Data – z tabulky/rozsahu (Údaje – od Tschopný/Randěl).
Poté přidáme vypočítaný sloupec s naší funkcí přes Přidat sloupec – Volání uživatelské funkce (Přidat sloupec – vyvolat vlastní funkci) a zadejte jeho argumenty:
Analýza e-mailových adres je složitější úkol a k jeho vyřešení existuje spousta regulárních výrazů různého stupně noční můry. Použil jsem jednu z jednoduchých možností – ne ideální, ale ve většině případů docela fungující:
[w|.|-]*@w*.[w|.]*
Jako oddělovač (vymezit) můžete zadat středník a mezeru.
Klikněte na OK a dostaneme sloupec s e-mailovými adresami extrahovanými z původního textu „kaše“:
Kouzlo!
PS
Jak se říká: "Neexistuje nic tak dobrého, co by nemohlo být ještě lepší." Power Query je cool sám o sobě a v kombinaci s regulárními výrazy nám dává naprosto nerealistický výkon a flexibilitu při zpracování jakýchkoli textových dat. Doufám, že Microsoft jednoho dne přidá podporu RegExp v aktualizacích Power Query a Power BI a všechny výše uvedené tanečky s tamburínou se stanou minulostí. No, zatím ano.
Ještě chci dodat, že je pohodlné hrát si s regulárními výrazy na webu https://regexr.com/ – přímo v online editoru. Tam v sekci Komunitní vzory Existuje obrovské množství připravených pravidelných sezón pro všechny příležitosti. Experimentujte – v Power Query je nyní k vašim službám veškerá síla regulárních výrazů!
- Co jsou regulární výrazy (RegExp) a jak je používat v Excelu
- Fuzzy textové vyhledávání v Power Query
- Sestavení tabulek z různých souborů pomocí Power Query