Power Query vo všetkých jeho variantách ponúka zaujímavú funkciu, ktorá je použiteľná na načítanie dát z neštruktúrovaných alebo zle štruktúrovaných dátových zdrojov, do peknej usporiadanej analytickej tabuľky. A to všetko bez znalosti jazyka M, jednoducho automatickým odvodením si vzorca podľa príkladu zadaného užívateľom. Preto sa na ňu teraz pozrieme.

Ukážeme si to na dátach, ktoré sme použili aj v predchádzajúcom článku – na zozname hlásení zo systému RASFF na tejto webovej adrese:

http://www.bezpecnostpotravin.cz/kategorie/hlaseni-v-systemu-rasff.aspx

Daná webstránka obsahuje po načítaní v prehliadači zhruba takúto tabuľku v strede obrazovky:

…ktorá po načítaní do Power Query, cez dátový zdroj Web, vyzerá takto:

Na analytické účely nič moc…

Teraz by sme potrebovali z toho prvého stĺpca vylámať číslo týždňa a rok, do dvoch osobitných stĺpcov. Tak ako v prechádzajúcom článku, kde sme to robili textovými funkciami v jazyku M.

Čo ale v prípade, že jazyk M neovládate?

Môžete sa pokúsiť použiť jednu nevinne vyzerajúcu, o to však prakticky využiteľnejšiu funkciu v Power Query – vytvorenie stĺpca podľa príkladu. Funguje to tak, že v Power Query zadáte vedľa existujúcich riadkov, vzorové hodnoty do nového stĺpca, pre 1 alebo viac riadkov, aké by ste z nich chceli vo výsledku vytiahnuť. A Power Query sa pokúsi automaticky odhadnúť, aký vzorec má použiť pre daný stĺpec. S tým, že táto funkcia je dosť inteligentná, a v Microsofte do nej investujú kopec úsilia takmer každý mesiac. Takže je stále lepšia a lepšia aj pre komplexnejšie prípady.

A ako ju použiť?

Keď ste v Power Query, tak najprv vyberte prvý stĺpec kliknutím naňho. V hlavnom menu potom kliknite na záložku “Pridať stĺpec“, a tam na “Stĺpec z príkladov” => “Z výberu“:

To nám napravo vedľa tabuľky otvorí ďalší stĺpec, ktorý bude tak trochu oddelený od zvyšku:

A tento stĺpec slúži na zadanie zopár prvých vzorových hodnôt, na základe ktorých sa Power Query pokúsi odvodiť hodnoty pre celý stĺpec.

Ak ste v predchádzajúcom kroku zadali “Z výberu“, tak sa to pokúsi odhadnúť z vybratého stĺpca, resp. vybratých stĺpcov. A ak ste vybrali “Zo všetkých stĺpcov“, tak sa to pokúsi odvodiť zo všetkých stĺpcov v aktuálnej tabuľke. Odporúčam používať skôr tú prvú možnosť a vybrať čím najmenší počet stĺpcov, aby to zbytočne Power Query nemýlilo.

V našom prípade chceme z toho prvého stĺpca vylámať číslo týždňa z textu. Zadáme teda do toho nového stĺpca hodnotu “31” (pretože na prvom riadku sa v texte nachádza táto hodnota), a stlačíme kláves Enter:

Power Query sa už len na základe tejto prvej hodnoty pokúsi odhadnúť vzorec, resp. zoznam transformácií, a v ďalších riadkoch pod zadanou hodnotou zobrazí navrhované hodnoty.

Ak sú v poriadku, tak to je všetko čo ste potrebovali spraviť. Ak nie sú v poriadku, tak zadajte niekoľko ďalších očakávaných hodnôt na ďalšie riadky, a sledujte na ešte nevyplnených riadkoch, či ďalšie napovedané hodnoty už zodpovedajú vašim očakávaniam. Zvyčajne stačí zadať 3-10 hodnôt aj v komplikovanejších prípadoch.

Tiež si všimnite, že v našom prípade to vygenerovalo priamo aj použiteľný vzorec v jazyku M, a zobrazilo ho hore nad tabuľkou:

Tento vzorec hovorí o tom, že Power Query má nájsť najprv 3. výskyt medzery v texte, po ňom výskyt bodky v texte, a vyrezať hodnotu medzi týmito dvoma pozíciami. Čo je v tomto prípade celkom pekné riešenie. Niekedy nemusí byť z dlhodobého hľadiska správne, lebo štruktúra dát sa môže zmeniť. Ale od toho máte v Power Query možnosť znova sa vrátiť do zoznamu operácií, a skúsiť túto alebo inú transformáciu aplikovať tak, aby vám to znova išlo 🙂 A nezabúdajte najmä na to, že benefitom tejto funkcie je najmä obrovská úspora času – namiesto niekoľkých minút strávených písaním vzorcov a študovania jazyka M, spravíte toto celé za 3 sekundy. A ak je výsledok dostatočne dobrý – čo vo väčšine prípadov je – tak proste idete ďalej a neriešite dokonalosť. Odmenou za použitie takýchto rýchlych funkcií je samozrejme zvýšená produktivita práce 🙂

Na záver nezabudnite ešte stlačiť tlačidlo OK, alebo na klávesnici skratku Ctrl+Enter. A premenovať výsledný stĺpec. Lebo až vtedy sa takýto stĺpec podľa príkladu naozaj pridá do tabuľky:

A samozrejme zmeniť dátový typ stĺpca na Celé číslo, nech to neťaháte do PowerPivotu, resp. Power BI, ako text. Výsledok by mal vyzerať nejako takto:

Chceli sme ale získať z údajov v prvom stĺpci aj číslo roka. To spravíme rovnako ako v predchádzajúcom prípade – pridáme ďalší stĺpec podľa príkladu, a do prvej bunky zadáme hodnotu “2018“, a potvrdíme tlačidlom OK, alebo na klávesnici skratkou Ctrl+Enter. Aj v tomto prípade to odhadne vzorec správne hneď po zadaní prvej hodnoty, podobne ako v prvom prípade:

Na záver samozrejme premenujeme výsledný stĺpec na Rok, prípadne zmeníme dátový typ stĺpca na Celé číslo, a máme hotovo:

Na konci si dáme samozrejme načítať dáta do dátového modelu, alebo do Excelu – podľa toho kde potrebujete používať tie dáta.

Takže takto jednoducho idú vyťahovať hodnoty aj z neštruktúrovaného, alebo slabo štruktúrovaného textu. Táto funkcia je úplne ideálna pri vyťahovaní údajov z rôznych produktových či iných katalógov na webe. A umožňuje vám aj pomerne rýchlo sa vysporiadať s prípadom, keď sú dáta v niektorom stĺpci zbalené do hromady znakov. Nezávisle od toho, či ste tieto dáta pôvodne ťahali z webu, z databázy či napr. z PDF súboru. Lebo pre Power Query, tak ako aj pri ostatných transformáciách, je úplne šuma fuk, odkiaľ ste to stiahli, a umožní vám použiť ľubovoľnú operáciu na ľubovoľných dátach, ktoré do neho dostanete. Čiže v tomto prípade vytvoriť stĺpce podľa príkladu. No nie je to nádhera? 🙂

Takýchto funkcií skrýva Power Query ešte hromadu. A tým, že je Power Query nielen v Exceli, ale aj v Power BI, SSAS Tabulare, Azure Analysis Services a dokonca po novom samostatne aj v cloude, a jeho výstupy sa už dajú aj samostatne ukladať ako zdroj dát pre iné systémy, tak čakajte, že v nasledujúcich mesiacoch sa mu ešte budeme dosť často venovať 🙂