Ukázky zajímavých úprav dat v Power Query

Autor: Miroslav Lorenc

Někdy se nám dostanou do ruky tabulky, se kterými se velmi špatně pracuje. Ne že by se v nich nedalo zjistit informace, ale jsou uzpůsobené spíše pro čtení člověkem či tisk než pro systematickou práci v Excelu či obdobném nástroji.

Aby šlo data analyzovat, vytvářet různé sestavy a vizualizace, potřebujeme data v dobře strukturované tabulce. Zdrojová tabulka níže je sestavena nesystematicky, navíc jde o dvě tabulky (prázdný řádek odděluje tabulky). Dále předpokládáme, že časem budeme mít v obdobné struktuře i data za další měsíce.

Naším cílem tedy bude vzít neuspořádanou skupinu buněk, vyčistit jí a mít z ní jednoduchou, čistou, tabulku nebo kontingenční tabulku.

V článku si ukážeme:

• Odebírání prázdných a mezisoučtových řádků
• Vyplňování prázdných buněk
• Spojování sloupců
• Převádění sloupců na řádky
• Načítání dotazů Power Query do kontingenční tabulky
• Počítanou položku použitou v kontingenční tabulce

Jak na úpravu tabulky

Označíme všechna data v oblasti A2:H16 a poté zadáme příkaz Data – Načíst a transformovat data – Z tabulky nebo oblasti. V dialogovém okně nebudeme zaškrtávat, že se má použít záhlaví. 

Otevře se Power Query, kde provedeme následující operace:

1. Označíme Sloupec1 a zadáme Transformace – Vyplnit dolů.

2. Odebereme sumarizační řádky pomocí filtru Sloupec1

3. Nemáme možnost vyplnit názvy měsíců doprava, proto provedeme transponování dat – nabídka Transformace – Tabulka – Transponovat.

4. U prvního sloupce provedeme opět vyplnění údajů (Transformace – Vyplnit dolů).

5. V tabulce chceme mít jen jedno záhlaví, proto označíme Sloupec1 a Sloupec2 a pomocí příkazu Transformace – Sloupec text – Sloučit sloupce spojíme texty obou sloupců, přičemž použijeme libovolný oddělovač a název sloučeného sloupce.

6. Tabulku transponujeme do původní podoby.

7. Jako názvy polí použijeme první řádek (Transformace – Tabulka – Použít první řádek jako záhlaví) a u prvních dvou sloupců upravíme texty.

8. Označíme první dva sloupce a zadáme příkaz z místní nabídky Převést další sloupce na řádky (nebo v pásu karet Transformace – Libovolný sloupec – Převést sloupce na řádky – Převést další sloupce na řádky).

9. Vybereme třetí sloupec a rozdělíme jej pomocí oddělovače (Transformace – Sloupec text – Rozdělit sloupec – Oddělovačem) a jako oddělovač zvolíme dvojtečku.

10. Pojmenujeme sloupce (dvojklikem na záhlaví sloupce).

11. Pomocí filtru ve sloupci Skutečnost / Plán odebereme položku Odchylka. To už jsme mohli udělat – např. v kroku 4 nebo odstraněním dvou sloupců v bodu 6. Odchylky, součty tržeb a nákladů a zisk/ztrátu z dat odstraňujeme proto, že tyto údaje si můžeme snadno vypočítat – nemá je tedy smysl přebírat.

12. Nyní máme z původní data tranformována do podoby, použitelné pro další analýzu. Zvolíme příkaz Domů – Zavřít a načíst.

Poklepáním na vlastnosti dotazu se můžeme kdykoliv vrátit k postupu nebo ho upravit.

Nyní v Excelu vytvoříme z transformované tabulky kontingenční tabulku – do oblasti sloupců dáme pole Tržba / Náklad a Položka, do oblasti řádků Měsíc a Skutečnost / Plán a ho oblasti Hodnot dáme pole Hodnota.

V dalším kroku naformátujeme vhodně čísla, upravíme popisky apod.

Následně do kontingenční tabulky vložíme počítanou položku Odchylka – ta bude rozdílem plánovaných a skutečných nákladů a tržeb.

Pozor – sčítání odchylek tržeb a nákladů nedává smysl.
Připojení výsledků za další měsíce:

• Zkopírujeme data za další měsíce ze skrytého listu Data_III-XII (buňky C2:AF16) za tabulku na listu Zdroj (do buňky I3 vložíme kopírované buňky jako hodnoty a tabulka by měla nové údaje “vstřebat”).
• Na listu Transformace klikneme do tabulky a poté Data – Dotazy a připojení – Aktualizovat vše.
• Na listu KT klikneme do tabulky a poté Analýza kontingenční tabulky – Data – Aktualizovat.
• Měsíce můžeme seřadit od A-Z (seřadí se podle logického pořadí ne podle abecedy, protože jsou ve vlastních seznamech).

Ke kontingenční tabulce přidáme průřezy a vyzkoušíme různé pohledy na data.

Dostali jsme nejen lepší pohled na dat, ale tento pohled si můžeme kdykoliv jednoduše změnit a data za další období můžeme jednoduše aktualizovat.