Občas pri príprave dát v Power Query potrebujete použiť regulárne výrazy, ľudovo regexy. Problém však je, že Power Query ich zatiaľ nepodporuje. Preto sa pozrieme, ako to obísť pomocou transformácií v R skripte, ktorý ich už dávno má.

V tomto návode si ukážeme, ako v Power Query viete použiť na extrakciu e-mailových adries z textu. Tento návod bude fungovať zatiaľ iba v Power BI Desktope, a možno v najnovšom Exceli 2019 a Office 365. Power Query v ostatných Exceloch to zatiaľ nepodporuje.

R je technológia, ktorá sa používa na pokročilé masové spracovanie dát, a zároveň aj na pokročilú vizualizáciu dát. Microsoft ju pred pár rokmi odkúpil spolu s jej tvorcom, firmou Revolution Analytics. Táto technológia sa používa na rôzne typy dolovania údajov, umelú inteligenciu, a rôzne vedecké výpočty. Existuje aj serverová verzia, ktorá sa volá buď R Server, alebo SQL Server R Services, alebo SQL Server Machine Learning Services, podľa toho aký je lunárny rok. Je dosť náročná na zvládnutie, takže si ju teraz nebudeme podrobne rozoberať.

S touto technológiou sa pracuje pomocou jej vlastného jazyka, tzv. R skriptu. Na ňu si potrebujete nainštalovať R klienta. Pre Power Query bude stačiť R Open, ktoré je zadarmo a zároveň ako open-source. Dá sa stiahnuť z tohto odkazu:

https://mran.revolutionanalytics.com/download

Po spustení inštalátora a dokončení inštalácie je potrebné ešte nastaviť Power BI Desktop. V ňom to spravíte cez menu Súbor => Možnosti a nastavenia => Možnosti, a tam v ľavom menu kliknete na Skriptovanie v jazyku R. Malo by to byť nastavené približne takto:

Potom si dáme vytvoriť alebo naimportovať takúto tabuľku (vy si dajte naimportovať tú svoju):

Zo stĺpca TextNaHladanie tejto tabuľky potrebujeme extrahovať e-mailové adresy do nového stĺpca, ak tam nejaké sú. Pre zjednodušenie budeme extrahovať iba prvú adresu, ak ich je tam viac.

Dáme teda upraviť tento dotaz v Power Query. Tam kliknite na záložku Transformovať, a potom na tlačítko Spustiť skript v jazyku R:

Malo by sa zobraziť takéto okno:

Ak sa v ňom zobrazuje hlásenie o tom, že R skript nie je nainštalovaný, tak si vyhľadajte na webe, ako to vyriešiť. Prípadne iba reštartovať Power BI Desktop.

Do tohto okna teraz prilepte nasledujúci skript, a stlačte OK:

# Údaje vstupu pre tento skript sú uložené v množine údajov ‘dataset’
library(stringr)
# Vytvor dataset, ktorý bude použitý na vrátenie výsledkov do Power Query
ResultSet<-data.frame(dataset)
# Vytvor funkciu Hladac, ktorou sa zo zadaného reťazca extrahuje zhodný podreťazec podľa regexu
Hladac = function(x) str_extract(x,’\\b[-A-Za-z0-9_.%]+\\@[-A-Za-z0-9_.%]+\\.[A-Za-z]+’)
# pridaj nový stĺpec EmailNajdeny do výsledku, a ulož tam výsledok funkcie Hladac
# vyvolaný nad stĺpcom TextNaHladanie
ResultSet[[“EmailNajdeny”]] <- apply(ResultSet[“TextNaHladanie”],1, function(x) Hladac(x) )

Riadky začínajúce mriežkou sú komentáre, kde máte vysvetlenie k jednotlivým krokom.

Ak ste predtým ešte nepoužívali R skript, tak vám pravdepodobne vyskočí nasledujúca chyba:

To je spôsobené tým, že nie je nainštalovaný balíček stringr, ktorý v sebe obsahuje onu podporu pre regulárne výrazy. V tom prípade pridajte na začiatok skriptu ešte tento riadok, ktorý tento balíček stiahne a nainštaluje z verejného repozitára balíčkov:

install.packages(“stringr”, repos=’http://cran.us.r-project.org’)

Niekedy je potrebné ešte stlačiť tlačidlo Obnoviť ukážku v hlavnom menu, aby sa obnovila tá správna konštelácia hviezd.

Po úspešnom spustení skriptu ten riadok odtiaľ môžete vymazať, aby sa pri každej obnove nepokúšal R skript znova nainštalovať tento balíček. Nie že by to bolo zlé, len zbytočne potom musíte vždy čakať, kým vyhľadá aktualizácie v tom verejnom repozitári. Čo po prvotnej inštalácii zvyčajne netreba.

Keď vám hore uvedený skript zbehne, tak výsledok by mal vyzerať takto:

V novom stĺpci EmailNajdeny budú nájdené e-mailové adresy. Alebo čokoľvek iné, čo len viete zadať cez regulárne výrazy 🙂

Takže takto jednoducho idú používať regulárne výrazy na transformáciu údajov v Power Query. Pomocou nich viete potom veľmi jednoducho extrahovať údaje aj z veľmi zle štruktúrovaných dát. Len je potrebné vedieť regulárne výrazy. Ale to je už iná téma 🙂

Prípadne, ak by vám to nestačilo, tak si prečítajte článok o tom, ako to isté spraviť v jazyku Python 🙂