Power Query pokročilé transformace dat - Jak řešit složité transformace dat v Power Query
Transformační funkce
Power Query nabízí širokou škálu funkcí pro transformaci dat. Při řešení složitých transformací je vhodné se seznámit s pokročilými funkcemi, jako jsou například „Merging Queries“ (slučování dotazů), „Grouping and Aggregating“ (seskupování a agregace) nebo „Conditional Columns“ (podmíněné sloupce). Tyto funkce vám umožní provádět komplexní transformace dat s vysokou přesností.
- Aggregating Queries: neboli připojení dotazu nám umožňuje rozšířit tabulku o další řádky. Často se toto dělá, když přidáváme data za předchozí roky. Tudíž rozložení sloupců zůstává stejné. Provádění této transformace není nijak obtížné, ale je potřeba dbát na to, aby tabulky měli stejný název, rozložení a počet sloupců.
Příklad: Máme v Power Query dva dotazy pro rok 2022 a 2023. Potřebujeme je sloučit do jedné tabulky obsahující všechny roky.
Na záložce Domů -> lišta “Kombinovat” -> “Připojit dotazy” -> “Připojit dotazy jako nové” -> zde zvolíme tabulky pro rok 2022 a 2023 -> Vznikne nám nová tabulka.
2. Merging Queries: jedná se o spojení 2 a více dotazů do jednoho. Jednoduše řečeno jde o složení tabulek do jedné tabulky. Očekává se zde že tabulky mají jiné sloupce až na jeden, podle kterého budou tabulky spojeny. Tomuto sloupci se říká klíč. Tímto klíčem jsme schopni zajistit správné párování dat. Jsou 2 základní druhy Sloučení dotazů. Left join a Right join. Jak z názvu může být patrné, tak Left join vezme všechny řádky z levé tabulky a spojí je dle daného klíče s odpovídajícími řádky z pravé tabulky. Zbylé zahodí. Right join dělá to samé, akorát z pravé strany.
Dalším speciálním spojením je Full outer join (úplné vnější), který spojí všechny řádky z obou tabulek. Opakem je poté Inner (vnitřní spojení), které nám vrátí pouze řádky, které byly spojené. V Power Query ještě můžeme narazit na Left/Right anti join. Tento druh spojení udělá to samé, co jeho jmenovec Left/Right join, ale poté ještě odstraní všechny výrazy spojené podle klíče. Respektive nechá jen ty řádky, které nenašli pár v druhé tabulce. Tento druh párování je vhodný při hledání chybných dat, které nemají pár.
Příklad: Máme tabulku “Financials” a “Country Code”. Obě tabulky obsahují klíčový sloupec Country. Do tabulky “Financials” potřebujeme nahrát sloupce z druhé tabulky.
Opět na záložce Domů -> lišta “Kombinovat” -> “Sloučit dotazy” -> “Sloučit dotazy jako nové” -> Jako první tabulka je vybraná “Financials” do které budeme vkládat tabulku “Country Code” -> Zvolíme se v první tabulce sloupec Country a v druhém okně si otevřeme tabulku “Country Code” v němž zvolíme také sloupec Country. Typ spojení zvolíme jako “Levé vnější”. Dole vidíme že všechny položky z první tabulky mají pár v tabulce druhé. -> Potvrdíme OK -> Vznikne nám nový dotaz obsahující data z druhé tabulky.
3. Conditional Columns: neboli podmíněné sloupce je funkce, která nám je schopná vytvořit sloupec na základě námi určených podmínek. Funkce “Podmíněný sloupec” nabízí široké možnosti jak nepodmiňovat nový sloupec s využitím dostupných sloupců. Operace pro podmínění jsou široké. Výhodné u textu může být operátor sledující obsah na začátku či na konci.
Vedle podmíněného sloupce také máme funkci “Sloupec z příkladů”, která je má oblíbená. Můžete si dokonce i jednoduše zvolit ze kterých sloupců budete příklady vytvářet. Poté píšete do řádků to, co se má napsat a Power Query funkci za vás vytvoří. Toto řešení je velice vhodné, pokud nejste znalí práce s kódem M.
Příklad: Potřebujeme si označit měsíce do kvartálů.
Využijeme již zmiňovaný “Podmíněný sloupec” -> na záložce “Přidání sloupce” -> “Obecné” -> “Podmíněný sloupec” -> Jak je vidět na obrázku tak “Název sloupce” volíme “Month Number” operátor větší nebo rovno a jelikož 4Q je v období od 10 do 12 měsíce dáváme 10 a Výstupem je 4Q. Obdobně pak pokračujeme s dalšími kvartály. Poslední už podmiňovat nemusíme, jelikož jsme vyfiltrovali vše ostatní. Pokud bychom si, ale nebyli jistí, že jsou všechny měsíce obsažené, doplníme i 4. klauzuli a do poslední kolonky dáme “null” nebo “0” abychom poznali řádek kde nám měsíc chybí. -> Právě jsme vytvořili sloupec s rozdělením měsíců do kvartálů.
Závěr
Power Query je silný nástroj pro pokročilou transformaci dat v Microsoft Excelu a Power BI. V tomto článku jsme se zaměřili na několik tipů a triků, jak řešit složité transformace dat v Power Query. Použitím pokročilých transformačních funkcí, vytvářením vlastních funkcí, použitím podmíněných výrazů, propojením sestavených kroků a využitím rozšíření můžete efektivně zvládat i nejnáročnější transformace dat.
