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.