Viackrát som sa na svojich kurzoch stretol s účastníkmi, ktorí si v oblasti spracovania a analýzy údajov vybrali zlý kurz. Bolo to spravidla kvôli nedostatočnému prehľadu o možnostiach, ktoré majú k dispozícií. Táto séria článkov má poskytnúť ucelený prehľad o analyzovaní údajov od základných konceptov ako je BI, cez nástroje použiteľné pri spracovaní importu až po SQL a iné pokročilé nástroje.
Tretia časť seriálu sa zaoberá nástrojom PowerQuery na automatizované získavanie údajov z ľubovoľného zdroja od databáz počnúc, webovými stránkami končiac. Jeho najsilnejšia stránka spočíva vo veľmi širokých možnostiach spracovania zdrojových údajov.
Import údajov do Excelu nie je nič nové
Jeden zo základných problémov pri pravidelnom analyzovaní údajov, je ich automatizované získavanie. Dôraz kladiem na slovíčko automatizované, nejdem tu riešiť metódu skopírovať – prilepiť. Pripájanie sa ku externým zdrojom nie je v Exceli nová vec. Nástroje na pripojenie externých zdrojov dát má Excel už poriadne dlho – dlhšie ako moja excel-pamäť siaha. Od verzie 2007 a novšej ich nájdeme v hlavnom menu na karte Údaje (viď obrázok).
Tieto nástroje sa dajú bez problémov využiť asi iba ak sú údaje v databáze alebo OLAP kocke. Pričom ak je naša databáza na inom ako MS SQL serveri, tak nás pravdepodobne čaká ešte jedna nepríjemnosť v podobe inštalácie ODBC ovládača na pripojenie sa ku danej databáze. V prípade textových súborov často narazíme na problémy v podobe dodatočných úprav funkciami ako som písal v predošlej časti. V prípade iných zdrojov, ako sú služby Facebook, Google, Salesforce, Dynamics CRM, Sharepoint, Exchange alebo akejkoľvek webstránky to nebude možné vôbec. Nenechajte sa popliesť tlačidlom z Webu. Je v súčasnosti nepoužiteľné. Ďalším problémom sú „kreatívne“ informačné systémy, ktoré nerobia export v štandardnej podobe. Štandardnou podobou chápeme hlavne súbory XML, prípadne CSV. V týchto súboroch je jasne definované štruktúra. Akonáhle ide o XLSX alebo iné textové súbory tak sa dostávame k tej kreativite. Takéto súbory bolo v minulosti treba dodatočne spracovávať manuálne alebo makrami. Tretím veľkým problémom starých nástrojov bola slabá možnosť filtrácie vstupných údajov. Je totiž rozdiel importovať milión alebo iba tisíc riadkov. V zásade jediná možnosť bola napísať priamo SQL dotaz v okne pripojenia, a aj tá bola k dispozícií iba ak sa išlo o databázu.
Čo je Power Query
Spoločnosť Microsoft vydala pre verziu 2013 doplnok Power Query. Tento doplnok sa dá zdarma stiahnuť a doinštalovať do verzie 2013 a vďaka spätnej kompatibilite aj do verzie 2010. Verzia 2016 má nástroje Power Query plne integrované v hlavnom menu Údaje v časti Získať a transformovať (viď obrázok).
Power Query je odpoveďou pre všetkých analytikov na všetky doteraz vymenované problémy. Vie importovať údaje z databázy, pričom podporuje všetky hlavné typy relačných databáz bez potreby inštalácie ODBC ovládačov. Vie importovať údaje zo súborov a to nielen textových, ale vie použiť aj xlsx ako zdroj údajov. Vie ťahať údaje z tabuliek umiestnených na webových stránkach. Vie získavať údaje ako je zoznam užívateľov domény. Vie sa pripojiť ku službám ako je Facebook, Google, Salesforce, Dynamics CRM, Sharepoint alebo Exchange a používať údaje z týchto služieb. Najsilnejšia stránka Power Query je však editor, ktorý umožňuje nakonfigurovať ako sa má Excel pri importovaní správať. Nejde o editor reálnych údajov. Je to editor skriptovacieho jazyka M (M language), ktorý vytvára importovací skript, aplikovaný na zdroj pri každej aktualizácií údajov. Akékoľvek zmeny vykonané v editore, ako je napr. odstránenie stĺpcov, filtrovanie riadkov alebo spracovanie textovej hodnoty v stĺpci sú zaznamenané ako kroky do importovacieho skriptu, ktoré sú vykonané priamo pri importe. Toto umožňuje analyzovať údaje priamo v kontingenčnej tabuľke, bez potreby ich importovania len kvôli dodatočnému spracovaniu. Ako je možno vidieť, na obrázku je okno editora veľmi blízke pracovnému prostrediu programu Excel a teda je omnoho bližšie obyčajným užívateľom, ktorí nemajú čas, ani chuť učiť sa programovanie makier.
Power Query a slušné údaje v Databáze
Pri práci s databázami môžeme v kľude zabudnúť na funkciu VLOOKUP, alebo akékoľvek iné vyhľadávacie funkcie. Klasický import databázových údajov (ak neberiem do úvahy priame písanie SQL dotazu) neumožňoval žiadne spájanie relačne prepojených tabuliek v pri tvorbe pripojenia. To znamenalo import a spájanie údajov pomalými vyhľadávacími funkciami. Power Query vie čítať relačné väzby v databáze a ponúka priamo výber stĺpcov, ktoré chceme zobraziť z relačne prepojenej tabuľky (tí čo poznajú SQL je to JOIN). Na obrázku je ukážka situácie, v ktorej je potrebné ku objednaným položkám v tabuľke order details získať informácie o názvoch produktov z relačne prepojenej tabuľky Products. Ako je možné vidieť, jedinú vec, ktorú potrebujeme, je otvoriť „filter“ v stĺpci Products a v ktorom si vyberieme stĺpce z tabuľky Products, ktoré chceme zobraziť. Nasledovať môže filter riadkov, ktoré chceme importovať aby sme do Excelu neprenášali zbytočne veľa údajov. Napríklad iba objednávky za rok 2016.
Power Query a neslušné údaje vo všakovakých súboroch
Veľká časť údajov ktoré sú bežne spracúvané v Exceli nepochádza priamo z databáz, ale z exportovaných XLS, XLSX alebo textových súborov rôznych typov. Klasický scenár môže vyzerať nasledovne. Z dvoch rôznych informačných systémov ste dostali dva textové súbory, ktoré musíme importovať a spojiť. Ak použijeme Power Query, tak nám stačí po nastavení pripojenia oboch súborov vytvoriť rovnaké spojenie „SQL JOIN“ ako keby boli obe tabuľky v databáze. Po potvrdení sa už konfigurácia zhoduje so scenárom Databáza. Stačí nám teda vybrať si, ktoré stĺpce chceme mať zobrazené v tabuľke položiek. (viď obrázok).
Tí znalejší problematiky možno budú protestovať, že objavujem Ameriku. Áno Excel vedel importovať hodnoty z CSV aj v minulosti. Nezabúdajme, že s pomocou Power Query máme možnosť spojenia oboch CSV priamo pri importe, filtrovanie nepotrebných riadkov a hlavne odpadá potreba importu, pretože vo väčšine prípadov nám postačuje len spojenie na súbor a analýza údajov v kontingenčnej tabuľke. Power Query vie týmto spôsobom spracovať aj súbory XLS a XLSX. Toto v praxi znamená, že nemusíme neštandardnú tabuľku v exportovanom Excel súbore upravovať manuálne alebo makrom. Postačí nám vytvoriť spojenie na daný súbor s neštandardnou tabuľkou, v rámci ktorého eliminujeme problémy ako sú napr. prázdne riadky a stĺpce alebo nesprávny tvar číselných a dátumových hodnôt. Okrem toho vieme pripojiť aj údaje jednej tabuľky na koniec druhej. Napr. spojiť tabuľku vystavených faktúr (exportovaných z jedného IS) s tabuľkou prijatých faktúr (exportovaných z druhého IS) do jednej tabuľky všetkých faktúr. Pre tých, čo poznajú SQL je to operácia UNION.
Čo ponúka Power Query obyčajnému užívateľovi Excelu?
Niektorí z vás, ktorí neriešia žiadne importovanie údajov, majú možno problém predstaviť si praktické použitie Power Query vo vašich jednoduchých súboroch. Pre vás mám jednoduchý príklad. Predstavte si tabuľku, v ktorej prepočítavate ceny z eur na libry alebo doláre. Pre praktickú každodennú prácu s tabuľkou potrebujete aktualizovať kurz dolára k euru na denno-dennej báze, ideálne bez užívateľského vstupu. Práve tu vieme využiť Power Query. Stačí si nájsť webovú stránku, ktorá každý deň zobrazuje aktuálny kurz napr. www.nbs.sk . Stránku použijeme ako zdroj údajov pre Power Query. V okne spojenia vyberieme správnu tabuľku na stránke. V okne Power Query nastavíme výber požadovaných údajov. Na záver exportujeme naše údaje do danej bunky. Táto metóda sa dá využiť na veľkú väčšinu webových stránok. Odpoveď na moju otázku z nadpisu je jednoduchá. Ponúka jednoduchý prístup k internetovým údajov priamo z Excelu. Čo je veľmi dôležité, Editor dotazov Power Query je navrhnutý v prehľadnom grafickom prostredí podobnému programu Excel, ktorý zvládne akýkoľvek trošku zdatnejší užívateľ Excelu. Na obrázku dolu je zachytený príklad importu kurzu zo stránky www.nbs.sk.
Čo ponúka Power Query SQL analytikom a programátorom VBA.
Je zrejmé že pri importovaní údajov z databázy je napísanie SQL dotazu omnoho rýchlejšie. Problém je že nestačí poznať SQL syntax, ale aj názvy objektov v databáze. Lenivejší analytici, ako som ja, určite ocenia možnosť si to doslova vyklikať. Nosnou témou pre SQL analytikov sú však určite ďalšie zdroje údajov. Konkrétne akékoľvek súbory – Exporty z informačných systémov , a Webové stránky. Programátori VBA určite ocenia jednoduchšie spracovávanie tabuliek v XLSX súboroch namiesto mordovania sa ladením makier. V oboch týchto skupinách dávam znovu do popredia možnosť importu webového obsahu. Power Query vie nielen čítať HTML tabuľky, ale obsahuje aj špeciálny analyzér XML a JSON kódu. Z uvedeného vyplýva, že máme celkom slušný parser webového obsahu zdarma.
Záverečné slovo.
Ako je vidno, nástroj Power Query, napriek tomu že sa už takto vo verzií Excelu 2016 nevolá, je naozaj silný nástroj na tvorenie dotazov (Power = silný, Query = Dotaz). Vzhľadom na svoje jednoduché grafické rozhranie je určite dostupný nielen pre Analytikov, ale aj pre bežných užívateľov z oblastí ako je marketing, financie, personalistika, atď. Skrátka, Power Query využijeme všade tam, kde sa objavujú údaje mimo náš Excel súbor a potrebujeme ich jednoducho a rýchlo dostať dnu. Niekto môže namietať, že Power Query je príliš neprehľadný ak potrebujeme relačne prepojiť údaje z väčšieho množstva zdrojov a nepoznáme z pamäti relačné vzťahy. Alebo napr. že nevie porovnať tržby za obdobia s tabuľkou plánovaných tržieb a zistiť ich plnenie, čo znamená kontingenčnú tabuľku a ďalšie vzorce. Áno, táto námietka je správna, a napriek tomu že obsahuje funkcionalitu Pivot-Unpivot, tak sa v zásade dá povedať že Power Query slúži hlavne na import údajov a nie na ich samotné analyzovanie. Na hĺbkovú analýzu a modelovanie údajov, ako je napríklad náš scenár s plnením plánu, slúži jeho partner v zbrani doplnok Power Pivot. Na jeho využitie a možnosti sa pozrieme v ďalšej časti.
Ak vás nástroj Power Query zaujal a chcete sa o ňom naučiť viac navštívte náš kurz Excel Databázy 1. Pozor tento kurz už predpokladá zvládnutie základov databáz a databázovej terminológie. Ak sa v týchto oblastiach strácate, tak odporúčam najskôr absolvovať kurz Excel 4.
autorom článku je: Ing. Patrik Toman
