Ne vždy forma zdrojových dat v Power BI vyhovuje našim potřebám, proto data dále zpracováváme, kombinujeme a upravujeme. A přesně tomu se věnuje tato série článků. Chcete-li se dozvědět více o Power Query a jeho rozhraní Power Query Editor, podívejte se na první část našeho mini seriálu.  

Pochopení datových transformací nám umožňuje nejen data čistit a upravovat na úrovni sloupce, ale také s nimi pracovat na úrovni všech dat v dotazu. Dnes se podíváme na složitější úpravy, jako je například vytvoření a zrušení kontingenčního sloupce, seskupení dat, či připojení a sloučení dotazů. Budeme upravovat celé dotazy, kombinovat data ze všech dotazů a kombinovat soubory. Také si porovnáme rozdíly mezi vytvářením nového sloupce pomocí jazyka M v Power Query a jazyka DAX v Power BI Desktop. 

Vytvoření nebo zrušení kontingenčního sloupce a seskupení dat 

První úprava, kterou si představíme, je vytvoření a zrušení kontingenčního sloupce (Pivot/Unpivot). Při vytváření kontingenčního sloupce přenášíme data z řádků do sloupců. Inspirací pro tuto část textu byl článek od mistra oboru – viz web RADACAD.  

Nejprve definujeme sloupec, jehož hodnoty řádků se stanou novými sloupci. V tomto případě se jedná o hodnotu měsíc. 

Originální data před vytvořením kontingenčního sloupce 

Následně v dialogovém okně vybereme sloupec s hodnotami, které mají být přiřazené pod nově vzniknuté sloupce (v tomto případě Budget Amount), a potvrdíme.  

Nastavení hodnot v dialogovém okně 

Výsledný transformovaný dotaz 

Při tomto příkazu je důležitá jedinečnost dat ve sloupci, jehož data definujeme jako nové sloupce v kroku jedna. Pokud se data opakují, může Power BI ve starších verzích systém ohlásit chybu.  

Originální data před vytvořením kontingenčního sloupce s opakovanou hodnotou 

Pokud je nám známo, že se tato data opakují a očekáváme, že se v tomto případě systém hodnoty bude agregovat, je dobré v druhém kroku nastavit správnou agregaci v rozšířených možnostech.  

Nastavení agregace dat v rozšířených možnostech 

A teď naopak. Zrušení kontingenčního sloupce je opačným procesem, kdy data ze sloupce přenášíme na úroveň řádků. Toto využijeme poměrně často, pokud je naším zdrojem dat např. kontingenční tabulka v Excelu. 

Originální data před zrušením kontingenčního sloupce 

Zrušení kontingenčního sloupce 

Výsledný transformovaný dotaz 

Pokud v tomto případě nejsou názvy sloupců jedinečné, tak se neobjeví chybové hlášení, ale vidíme opakované hodnoty, což musíme mít na paměti při další práci s těmito daty.  

Originální data s duplikovaným sloupcem 

Výsledný transformovaný dotaz 

Další často používanou transformací pro odstranění duplicit anebo optimalizaci granularity dat je agregace řádků v Power Query pomocí příkazu Seskupit podle. Ten najdeme na kartě Transformovat v Power Query Editoru anebo po pravém kliknutí na vybraný sloupec.  

Nejprve určíme sloupec, na základě kterého chceme data seskupit. Následně se otevře dialogové okno, které nám nabídne možnost základního anebo rozšířeného seskupení. Základní seskupení umožňuje sloučit řádky podle jednoho sloupce a vybrat jednu agregaci z nabízených možností. Rozšířené seskupení umožňuje definovat vícero sloupců pro agregaci a pro každý sloupec definovat vlastní agregaci. Dostupnými operacemi/agregacemi jsou: 

• Počet řádků 
• Součet 
• Průměr 
• Medián 
• Min 
• Max 
• Počet jedinečných řádků 
• Všechny řádky 

Část originálních dat před seskupením 

Seskupit podle – dialogové okno 

Výsledný transformovaný dotaz 

Spojování dotazů 

Více tabulek můžeme spojit do jedné pomocí transformačních kroků: Připojit dotazy (Append Query) a Sloučit dotazy (Merge Query). Připojit dotazy funguje na podobném principu jako příkaz UNION ALL a sloučit odpovídá Příkazu JOIN v SQL.  

Při připojení dotazů vybereme dotaz a následně daný příkaz. Tím můžeme rozhodnout, zda další dotazy připojíme do existujícího, anebo vytvoříme nový dotaz, přičemž původní dotaz zůstane nezměněný. Následně se nám otevře dialogové okno, ve kterém vybereme, zda chceme spojit dva nebo vícero dotazů a ty následně specifikujeme. Je potřeba mít na paměti, že při této operaci nedojde k odstranění duplikátů.  

Dva dotazy k připojení 

Připojit dotaz – menu 

Výsledná tabulka 

Stejně tak je důležité mít na paměti že pro správné fungování je potřebné, aby jména sloupců mezi tabulkami byly stejná. Pokud nejsou názvy sloupců konzistentní, připojení stále funguje ale vytvoří se nové sloupce a doplní hodnotu null.  

Připojení sloupce s nekonzistentním názvem 

Zdroj: Append queries – Power Query | Microsoft Learn 

Sloučení dotazů je operace, která spojí dva dotazy na základě shody mezi daty v řádcích. Stejně jako v předchozím případě, se tento příkaz nachází na kartě Domov. Vybereme dotaz, který stojí na levé straně sloučení (dotaz 1) a rozhodneme, či chceme dotazy sloučit do vybrané anebo nové tabulky.  V dialogovém okně, které se následně otevře, nadefinujeme tabulku stojící na pravé straně sloučení (dotaz 2) a vybereme druh spojení: 

• Levé vnější (všechny z prvního, shodující se z druhého) 
• Pravé vnější (všechny z druhého, shodující se z prvního) 
• Úplné vnější (všechny řádky z obou) 
• Vnitřní (jen shodující se řádky) 
• Levé anti (jen řádky v prvním) 
• Pravé anti (jen řádky v druhém) 

Druh spojení při sloučení dotazů 

Zdroj: JOIN OPERATOR (udel.edu) převzato z Append vs. Merge in Power BI and Power Query – RADACAD 

Zároveň určujeme slučovací kritérium, tedy sloupec/sloupce (vícero sloupců vybereme pomocí přidržení klávesy CTRL a kliknutím na sloupec). Sloupce, které tvoří pár, musí mít stejný datový typ, není tak možné vybrat jeden sloupec s datovým typem text a jiný s datovým typem číslo. Více o datových typech najdete v článku Dátové transformácie v Power BI (Časť 1).  

Příklad sloučení dotazů 

Volba pro výběr zobrazených sloupců po sloučení 

Výsledný dotaz po rozbalení nových sloupců 

Při vytváření reportů se můžeme setkat také s případem, kdy se zdrojová data nacházejí ve vícerých souborech se stejnou logickou strukturou umístněných v jedné složce a je nejvíce efektivní je zkombinovat do jednoho dotazu. Příkladem mohou být měsíční exporty dat, které se ukládají do separátních souborů. Tento požadavek je možné vyřešit využitím další z funkcionalit Power BI pro kombinovaní souborů ze složky. Tento proces obsahuje 3 kroky: 

1. Připojení se na složku a definice cesty

2. Po výběru/kliknutí na Kombinovat, se otevře dialogové okno, kde specifikujeme, který z dostupných souborů slouží jako příklad pro ostatní soubory ve složce. Na základě toho Power BI vybere správný druh konektoru, jako například Text / CSV, Excel, JSON anebo XML. V tomto kroku také můžeme definovat vynechaní těch souborů, které by po načítaní skončily chybovým hlášením. 

Dialogové okno pro kombinovaní souborů 

Zdroj: Combine files overview – Power Query | Microsoft Learn 

3. Následně je vytvořený výsledný dotaz. V Power Query Editore vidíme, že spolu s novým dotazem obsahujícím data ze soborů se nám vytvořil další dotaz pod názvem Transform Sample File, který obsahuje všechny kroky aplikovatelné na extrakci souboru, který jsme vybrali jako příklad pro ostatní. Helper Queries, nacházející se nad ním, jsou funkčními dotazy parametrizujícími vstupy do Transform Sample File. 

Výsledný dotaz v Power Query Editoru 

Zdroj: Combine files overview – Power Query | Microsoft Learn 

Parametry a opakované použití dotazů 

Parametr v Power Query slouží pro dynamickou změnu výstupu na základě vstupní hodnoty a může obsahovat referenci na datové zdroje, filtry nebo skupiny. Správa parametrů je dostupná na kartě Domov v Power Query Editoru, kde můžeme spravovat a upravovat existující, nebo vytvářet nové parametry.  

Správa parametrů v Power Query Editoru 

Nový parametr vzniká několika způsoby, a to jako součást připojení na některé datové zdroje, jako je tomu vpřípadě načítání dat ze složky, zkonvertováním dotazu na parametr anebo manuálním vytvořením.  

Zkonvertovaní dotazu na parametr 

Zdroj: Parameters – Power Query | Microsoft Learn 

Vytvoření nového parametru 

Zdroj: Parameters – Power Query | Microsoft Learn 

Jeden ze scénářů, kde je parametr efektivním řešením, je přepínání mezi dvěma zdroji dat se stejnou strukturou (např. mezi testovací a produkční databází nebo mezi různými schématy v databázi). V prvním kroku se připojíme na testovací databázi a následně v Power Query Editoru vytvoříme parametr s názvem této databáze. 

Vytvoření parametru 

V nastavení dotazu, kterým se připojujeme na databázi, změníme zdroj na vytvořený parametr v prvním kroku připojení dotazu (Source > ozubené kolečko pro nastavení). 

Změna nastavení zdroje na parametr 

Pokud se v buducnosti budeme chtít přepojit na produkční databázi, bude dostatečné změnit název databáze v Power BI Desktopu a není potřeba dělat žádné další úpravy v Power Query Editoru. 

Změna hodnoty parametru v Power BI Desktopu 

Jinou možností optimalizace je využití již vytvořených dotazů na další datové transformace a to tak, že původní dotaz duplikujeme nebo se na něj odkážeme.  

Kopírovaní, duplikace a přidání odkazu na dotaz 

Duplikace dotazu vytváří kopii všech existujících kroků a duplikát je izolovaný od původního dotazu, takže žádné změny na původním dotazu se nepřenáší do duplikovaného dotazu. V případě reference vzniká nový dotaz jen s jedním krokem, referencí do původního zdroje. Nový dotaz není izolovaný a jakékoli změny na původním dotazu se projeví také v dotazu referencovaném.  

Třetí možností je kopírovaní dotazu. Na rozdíl od duplikace se nevytváří jen jeden nový dotaz totožný s původním, ale zkopírují se všechny zdrojové dotazy.  Pokud například kopírujeme dotaz, který vznikl jako reference, tak se nezkopíruje jen tento jeden dotaz, ale i originál, který sloužil jako reference. 

Vytváření nových sloupců v jazyku M a v DAX  

V článku Datové transformace v Power BI (Část 1) jsme si uvedli, že jazyk M je funkčním jazykem do kterého jsou propisované všechny operace odehrávající se v Power Query. Není to však jediný jazyk, se kterým pracujeme při budovaní reportů v Power BI. Dalším jazykem je DAX, což je zkratka pro Data Analysis eXpression. Oba jazyky se vyvíjely nezávisle na sobě, proto se můžeme setkat s tím, že stejnou operaci je možné vykonat jak v Power Query Editoru pomocí jazyka M, tak i v Power BI Desktopu pomocí jazyka DAX. Jednou z těchto operací je i vytvoření nového kalkulovaného sloupce. Otázkou zůstává, kterou cestu bychom měli využít.  

Odpověď však není jednoznačná, protože závisí na tom, čeho chceme dosáhnout. Pokud připravujeme data pro budoucí model, je lepší variantou využít jazyk M a sloupec vytvořit v Power Query. V případě, že budujeme kalkulace nad datovým modelem, obvykle se doporučuje použít DAX. Zatímco v případě použití M totiž ještě neexistují definované vztahy mezi tabulkami v modelu, v případě DAX už tyto vztahy můžeme využít pro vytvoření sloupce (například užití Příkazu RELATED v DAX). Na druhou stranu s pomocí jazyka M můžeme lehce manipulovat s daty pomocí přednastavených operací, které jsme si popsali v tomto článku.  

Závěr 

Power Query Editor, jako i jazyk M, nám poskytuje široké spektrum možností pro práci s daty. V tomto článku jsme si ukázali vytvoření a zrušení kontingenčního sloupce i seskupení dat. Dále jsme se zaměřili na připojení a sloučení dotazů a kombinovaní souborů ze složky, které nám umožňují spojit více datových zdrojů do jednoho dotazu. Věnovali jsme se vytvoření parametrů a jejich využití na přepínání mezi zdroji dat a kopírování, referenci a duplikovaní dotazů v Power Query Editoru. 

Zdroje:
Append queries: Append queries – Power Query | Microsoft Learn
Append vs. Merge in Power BI and Power Query: Append vs. Merge in Power BI and Power Query – RADACAD
Combine files overview: Combine files overview – Power Query | Microsoft Learn
Grouping or summarizing rows: Grouping or summarizing rows – Power Query | Microsoft Learn
Change the Source of Power BI Datasets Dynamically Using Power Query Parameters: Change the Source of Power BI Datasets Dynamically Using Power Query Parameters – RADACAD
M or DAX? That is the Question! M or DAX? That is the Question! – RADACAD
Merge queries overview: Merge queries overview – Power Query | Microsoft Learn
Pivot and Unpivot with Power BI : Pivot and Unpivot with Power BI – RADACAD
Pivot columns: Pivot columns – Power Query | Microsoft Learn
Unpivot columns: Unpivot columns – Power Query | Microsoft Learn 

Autoři:
Tatiana Kardošová
Kristina Nohavicová
kristina.nohavicova@dolphinconsulting.cz