V minulém díle seriálu jsme si řekli, že PowerQuery (PQ) je nástroj určený k zpracování a transformacím dat. Přesně ten nástroj, který potřebujete použít pokud data nejsou ve správném tvaru. V dnešním díle si to rozebereme o něco podrobněji. Jak to funguje, co s tím jde dělat a nebude chybět ani krátké demo prezentující pár užitečných funkcí.

Jak to funguje

PQ je doplňkem do MS Excel 2013 a 2010, instaluje se samostatně. Umí načítat data z různých zdrojů buď do listu Excelu, nebo do PowerPivot modelu. Designovou fázi bych lehce přirovnal k rekordéru maker. Klikáte v grafice, v pozadí se generuje kód, který můžete v editoru upravit. Jazyk který se zde používá se jmenuje “PowerQuery formula language” původně známý pod označením “m”. Jazyk funguje obecně, vy si vyberete transformaci, PQ dotaz přeloží do nativního jazyka zdroje a tento se vykoná proti zdroji.

Co s tím jde dělat

V prvé fázi načíst data ze zdroje a že jich je požehnaně. Základna se postupem času rozrůstá a přibývají zdroje nové. Jen za posledních pár měsíců přibyl konektor na SAP, SQL Server Analysis services a Sales Force.

Databázové zdroje: MS SQL, Access, Azure SQL, Teradata, Oracle, MySQL atd.

Soubory: txt, csv, xls, xml… v této kategorii je velmi zajímavá možnost načíst data ze složky, pokud mají soubory stejnou strukturu (v dřívějších verzích Excelu to vyžadovalo kódování ve VBA).

Webové zdroje: načítání z konkrétního webu když zdroj znáte (jako třeba načtení kurzovního lístku ČNB v demonstraci), případadně online vyhledávání, když si zdrojem nejste jistí. Funguje zde i vyhledávání datasetů, které vysdíleli vaši kolegové.

Další zdroje: SharePoint list, OData feed, Windows Azure market place (v poslední době přibývají velmi zajímavé možnosti díky Azure Machine Learning algoritmům, komfortně z PowerQuery můžete dělat dataminingovou analýzu nákupního košíku, sentiment analýzu komentářů a další… třeba v nějakém jiném článku). Dále Exchange server, Active directory, Facebook….

Na typu datového zdroje dost záleži, podle něj se odvíjí zbývající proces. PQ může pomoci načíst data z databáze i uživatelům, kteří neovládají SQL. Pokud vyberete některou zdrojovou tabulku a na této existují cizí klíče, můžete se prokliknout do tabulek souvisejících. V transformacích data vyfiltrovat, agregovat a vyčistit až do požadované podoby.

Transformací je k dispozici opravdu hodně a jsou poměrně intuitivní, pokud bych měl některé vyzdvihnout coby oblíbené, určitě bych nevynechal následující:

• unpivot – kategorie na řádcích, měsíce na sloupcích v průsečíku hodnota, jako v kontingenční tabulce, bohužel s tímto se databázově špatně pracuje. Transponování matice v klasickém Excelu nepomůže.
• Unpivot v SSIS a nebo v TSQL jsou zbytečně složité a nepružné. V PQ označím sloupce, kliknu pravým unpivot a je to.
• change data type using locale – jednou přitečou čísla v americkém formátu s desetinou tečkou, z druhého zdroje to leze česky s čárkou, datumy prohazují dny a měsíce, jak to řešit? Vyberete sloupec a řeknete mu tohle je číslo/datum v daném formátu
• remove other columns – vybere to chcete nechat, smažete zbytek, hodilo by se i v jiných nástrojích split to columns – by delimiter – first left space – vychytané rozdělování do sloupců
• remove duplicates – deduplikace záznamů

A dalo by se pokračovat. Po tom, co „nahrajete“ zpracování dat nad statickým vzorcem, můžete překlopit skript v advanced editoru na funkci

Deklarace proměnné: (ROK)=>

Jazyk PQ je case sensitive, a náchylný na datové typy, pro textové slučování je potřeba prvně překonvertovat čísla na textové řetězce, dynamicky složená webová adresa by mohla vypadat takto
=“http://www.data.cz/rok“ & Number.ToText(ROK) & “.html”
Dotaz může vytvořit jeden člověk, tento vypublikovat do online katalogu a tento je poté přístupný ostatním uživatelům. Výstup z datasetu může skončit na listu, nebo v modelu PowerPivot, kde
bychom dodělali chybějící logiku. O tom ale příště.

Za tím se můžete podívat na video s demonstrací pro načítání dat kurzovního lístku ČNB.