Co je Power Query

V minulém článku jsme psali o Power Pivotu, jednom z nejdůležitějších doplňků pro takzvané "Self-service BI" v Excelu. Power Pivot je základním kamenem pro modelování dat v Excelu, ale k dispozici jsou také další doplňky, které výrazně pomáhají při vytváření reportů v Excelu. V tomto článku se hlouběji podíváme na doplňek Power Query (dříve Data Explorer), který byl představen na konci roku 2013.

Power Query

Power Query (dále jen PQ) je primárně zaměřen na import dat do Excelu. Můžete si říct, že to je v Power Pivotu taky možné, tak proč si instalovat další doplňek. Protože PQ neslouží pouze k import dat, díky němu máte možnost data upravovat, transformovat a vybírat právě ta, která vás zajímají. Power Query se tímto stáva mocným ETL nástrojem pro import dat do datového modelu (nebo do listu v sešitě) z různých zdrojů.

Oproti Power Pivotu se zvýšilo množství externích datových zdrojů, nyní je možné jednoduše stahovat data z MySQL/Postgre databáze, Hadoop, Windows Azure HDInsight, SAP BI Universe, Facebooku, nebo z jakékoliv webové stránky. Takže si například můžete najít zajímavá data na wikipedii, například stránku o mistrovství světa v basketbalu, Power Query analyzuje celou stránku a pokud najde nějakou tabulku na stránce nabídne ji jako datový zdroj. Velice jednoduché a rychlé. Nabízí se také možnost využít službu hledání ve veřejně dostupných datech na internetu, která ale prozatím funguje převážně jen pro americká data.

Při výběru jednoho z datových zdrojů se otevře samostatné okno, ve kterém můžete data transformovat a následně je uložit. Při práci s daty v Power Query se všechny vaše akce ukládají jako seznam po sobě jdoucích kroků, takže přehledně vidíte, jaké transformace jste s daty provedli. Tudíž máte přehled co se s daty stalo od chvíle, kdy jste je nahrály do PQ. Možnosti transformací jsou velké, od filtrování řádků, odstranění duplicit, rozdělení sloupců, nahrazení hodnot až po transformování textu a vytváření dalších sloupců. Vše můžete vytvořit pouhým klikáním na položky v liště nebo pomocí nového programovacího jazyka M. Po dokončení všech kroků můžeme vybrat zda chceme výsledek uložit do listu v Excelu nebo do datového modelu. Následně se celý dotaz uloží, takže se k němu můžeme kdykoliv vrátit a změnit nebo přidat věci, které v něm chybí. Ve spojení s Power BI můžeme tyto dotazy sdílet a nechat další spolupracovníky využívat naše vytvořené dotazy. Více to bude rozvedeno v jednom z dalších dílů se zaměřením na Power BI.

Power Query také podporuje takzvaný "query folding", který výrazně zrychluje práci s daty a načítání do datového modelu. Pokud jsme připojeni například k MS SQL Serveru, "query folding" funguje tak, že interpretuje naše kroky přímo do SQL Serveru a ten vrátí jen data, která chceme. Když například importujeme tabulku z SQL Serveru do PQ, poté vyfiltrujeme jen některé řádky, nahradíme určité hodnoty, odstraníme nepotřebné sloupce a pak uložíme do datového modelu, tak při příštím volání stejného dotazu se tyto kroky nebudou odehrávat na našem počítači v PQ, ale automaticky se vytvoří SQL dotaz na zdrojovou databázi, která vrátí požadovaný výstup. Tím je načítání dotazu rychlejší než kdybychom vše řešili až po stažení všech dat do PQ.

Jazyk M

Jazyk M je procedurální a vychází z jakyza C#. Všechny kroky, které provedete můžete zapsat také ručně pomocí tohoto jazyka. Nabízí se vám tedy mocný nástroj na úpravu a přípravu dat pro samotné reporty. Jedním z příkladů může být vytvoření kalendářní tabulky, jen za pomocí Power Query. Stačí použít předdefinované funkce List.Dates, které se předají argumenty a ta vygeneruje seznam kalendářních dat. Tento seznam už můžete pro jednotlivé dny rozšiřovat o další sloupce jako jsou názvy dne v týdnu, názvy měsíce, rok a další informace, které jsou v kalendářní tabulce důležité.

Další možnost použití je naprogramování funkcí, které mohou vypočítat dodatečné hodnoty do tabulky. Tato funkce se chová jako jakýkoliv jiný PQ dotaz, jen čeká na vstupní hodnotu. Můžeme si tak například představit funkci, která po zádání PSČ vrátí kraj ze kterého PSČ je. Tento dotaz můžeme připojit k tabulce kontaktů, kde každý kontakt má uvedenou adresu. Po načtení dat připojíme nový sloupec, který bude volat naši vytvořenou funkci a ta bude vracet název kraje do kterého uvedený kontakt spadá. Vše stále běží jen v Excelu.

Power Query stále není ve všech směrech uplně dokonalý, ale Microsoft se ho snaží stále zlepšovat. Přibližně každý měsíc vychází novější verze, do které jsou přidány nové funkce a možnosti, nebo opraveny některé chyby verzí starších. Díky Power Query můžeme nahrávat data do Power Pivot modelu už ošetřená a zlepšit tím výsledný datový model a jeho použití. Pro Power Pivot, ve kterém nebyla možnost data vůbec nijak měnit, je to velice užitečný nástroj, který ušetří spousty práce.