Niekedy nedostanete dáta zrovna v peknom formáte. A niekedy už ani omylom v použiteľnom formáte. Typický príklad je taký, keď vám niekto pošle dáta vo formáte kontingenčnej tabuľky, kde jednotlivé stĺpce na analýzu sú v riadkoch aj stĺpcoch tabuľky. Čo je fajn, keď máte kontingenčku, ale už nie až tak fajn, ak to chcete analyzovať a spájať s ostatnými dátami. Preto takéto kontingenčné dáta musíte prerobiť na normálnu tabuľku.

Majme povedzme takúto tabuľku:

Tam vidíte, že sú tam obraty pre jednotlivých obchodníkov po rokoch. A my by sme potrebovali teraz dostať tie roky zo stĺpcov do riadkov, aby sme to mohli spájať v PowerPivote napr. s plánovacou tabuľkou, a vyhodnocovať plnenie plánu. To sa samozrejme dá spraviť aj v samotnom Exceli, nejakou starou skrytou funkcionalitou z Excelu 2007, ktorá sa okrem iného aj dosť krkolomne používa, a bohvie či je ešte aj v najnovšom Exceli. Preto radšej použijeme Power Query. Výhodou je to, že toto vám bude fungovať rovnako okrem Excelu aj v Power BI.

V prvom kroku teda naimportujeme našu tabuľku do Power Query.

Pokračujte teda takto:

• ak máte Excel 2010/2013, oba s doplnkom Power Query, tak v hlavnom menu choďte na záložku Power Query, a tam kliknite na tlačítko nazvané “Zo súboru”, a pod ním na položku “Z Excelu”,
• ak máte Excel 2016, tak v hlavnom menu choďte na záložku Údaje, a tam kliknite na Nový dotaz => Zo súboru => Zo zošita,
• ak máte Excel 2019, tak v hlavnom menu choďte na záložku Údaje, a tam kliknite na Získať údaje => Zo súboru => Zo zošita,
• ak máte Power BI Desktop, tak v hlavnom menu kliknite na Načítať údaje => Excel.

Následne sa vám otvorí okno na výber excelovského súboru. Vyberte tam teda svoj súbor, ktorý idete importovať, a stlačte OK:

Namiesto tohto postupu, ak ste v tom istom Exceli, sa to dá spraviť aj tak, že keď ste v tej tabuľke (ktorú ste predtým označili a vytvorili cez klávesovú skratku Ctrl+T), tak si na tej záložke Power Query, resp. Údaje, nájdite tlačítko “Z tabuľky”, resp. “Z tabuľky alebo rozsahu”. Presný názov zasa závisí od verzie Excelu 🙂 Po jeho stlačení sa daná tabuľka naimportuje do Power Query.

Keď to teda spravíte prvým alebo druhým postupom, tak sa otvorí Editor Power Query, a bude to vyzerať takto:

Tu už stačí iba označiť správne stĺpce, a zavolať správnu funkciu, a máme hotovo.

Označte teda tie 3 stĺpce s rokmi, choďte v hlavnom menu do záložky Transformovať, a tam kliknite na tlačítko “Zrušiť kontingenčnosť stĺpcov”:

…a tadááá, máme želaný výsledok, ktorý sa už ľahko bude analyzovať:

Stačí si už len premenovať stĺpce na normálne názvy. Takže už to nemusíte otrocky prerábať, manuálne či rôznymi makrami, do takejto podoby. Stačí použiť Power Query, a za pár sekúnd to máte hotové.

Nakoniec nezabudnite stlačiť tlačítko “Zavrieť a načítať”, resp. “Zavrieť a použiť”, na záložke Domov, aby sa vám výsledok uložil do Excelu, resp. Power BI Desktopu.

Ešte sa môže stať to, že keď toto budete robiť pravidelne, resp. nastavíte tomu automatickú aktualizáciu, tak vám v budúcnosti pribudnú ďalšie stĺpce (napr. po roku pribudne štatistika za ďalší rok):

Keby ste to robili tak, ako som písal vyššie, tak by ste nedostali zrovna pekný výsledok:

Ten nový stĺpec za rok 2018 sa “neprerobí” do riadkov tak, ako predchádzajúce, pretože Power Query si pamätá, že malo otočiť iba tie 3 stĺpce, ktoré ste pôvodne označili. Dá sa to samozrejme zmeniť v paneli operácií napravo, ale každý rok sa vám asi nechce na to myslieť… Preto to ideme spraviť inak.

Po naimportovaní pôvodnej tabuľky tentokrát neoznačte tie 3 stĺpce, ktoré chcete otočiť do riadkov, ale označte tie stĺpce, ktoré NEchcete otočiť do riadkov. Čiže v našom prípade označíme prvý stĺpec, potom v hlavnom menu prejdeme do záložky Transformovať, a tam kliknite na tú malú šípočku vedľa tlačítka “Zrušiť kontingenčnosť stĺpcov”, a vyberte položku “Zrušiť kontingenčnosť ostatných stĺpcov”:

Výsledok bude samozrejme rovnaký ako v predchádzajúcom prípade:

Rozdiel však zbadáte, ak do tej pôvodnej tabuľky pridáte ďalší stĺpec s dátami za rok 2018. Výsledok v tomto prípade bude už správny:

Je to kvôli tomu, že sme povedali, že chceme tentokrát otočiť do riadkov všetky ostatné stĺpce okrem označených. Takže tentokrát vám tam môže pribudnúť aj 10 stĺpcov s dátami za ďalšie roky, a otočí to správne.

Čo však v prípade, že máme viac hardcore kontingenčné dáta?

Napríklad takúto chrumkavú tabuľku, kde kontingenčné dáta pochádzajú z kontingenčky poskladanej z viacerých kritérií:

Tam to už tak jednoduché nebude. Neznamená to však, že to bude nemožné – stačí iba ísť na to trochu inak.

Naimportujeme teda takúto tabuľku do Power Query. Štartovacia čiara je tentokrát takáto:

Teraz si hlavičky tabuľky posunieme do prvého riadku tabuľky. Spravíme to tak, že v hlavnom menu, na záložke Domov, klikneme na tú malú šípočku vedľa tlačítka “Použiť prvý riadok ako hlavičky”, a vyberieme tú druhú možnosť – “Použiť hlavičky ako prvý riadok”:

Výsledok bude vyzerať takto:

Teraz prehodíme medzi sebou riadky a stĺpce tabuľky. V hlavnom menu, na záložke Transformovať, klikneme na tlačítko Transponovať:

Výsledok bude vyzerať takto:

A tu už sa začína črtať riešenie. Najprv teda povýšime prvý riadok tabuľky na jej hlavičky. Kliknite teda v hlavnom menu na tlačítko “Použiť prvý riadok ako hlavičky”:

A výsledok bude takýto:

Teraz už jednoducho otočíme posledné 3 stĺpce do riadkov, tak ako aj v predchádzajúcom príklade, keď sme otáčali klasické kontingenčné dáta. Čiže:

• buď označte posledné 3 stĺpce s obchodníkmi (ak budete mať fixný zoznam obchodníkov), choďte v hlavnom menu do záložky Transformovať, a tam kliknite na tlačítko “Zrušiť kontingenčnosť stĺpcov”,
• alebo označte prvé dva stĺpce (ak s vám zoznam obchodníkov bude časom rozširovať), v hlavnom menu prejdite do záložky Transformovať, a tam kliknite na tú malú šípočku vedľa tlačítka “Zrušiť kontingenčnosť stĺpcov”, a vyberte položku “Zrušiť kontingenčnosť ostatných stĺpcov”.

Výsledok bude vyzerať takto:

Skoro dokonalé, len to má dve chybičky krásy: stĺpce si ešte musíte premenovať na iné názvy, a v prvom stĺpci sme očakávali roky, ale sú tam roky aj názvy “StĺpecX”. Tie názvy pochádzajú pôvodne z prázdnych buniek v pôvodných dátach, a tie musíme nahradiť hodnotami rokov.

Spravíme to vo všeobecnosti podmieneným vypočítaným stĺpcom cez M skript. Ale v tomto prípade to ide spraviť aj jednoduchšie. Spravíme to tak, že zmeníme dátový typ stĺpca s rokmi na celé číslo – kliknutím na tú ikonku naľavo v hlavičke stĺpca, a výberom položky “Celé číslo”:

Výsledok bude takýto:

Tam síce teraz máme chyby namiesto hodnôt “StĺpecX”, ale to je presne to čo sme chceli.

V ďalšom kroku nahradíme tieto chyby prázdnymi hodnotami. Označte prvý stĺpec, prejdite v hlavnom do záložky Transformovať, a tam, vedľa tlačítka “Nahradiť hodnoty”, kliknite na tú malú šípočku, a vyberte položku “Nahradenie chýb”:

Vyskočí na vás takéto okno, kde zadajte do políčka hodnotu “null”, ktorá zodpovedá prázdnej hodnote, a stlačte OK:

Vo výsledku sa nám teraz nahradia chyby prázdnou hodnotou:

No a do tých prázdnych hodnôt teraz rozkopírujeme hodnoty rokov z predchádzajúcich riadkov, tak ako v predchádzajúcom článku – v hlavnom menu klikneme na tlačítko Vyplniť, a následne vyberieme položku Nadol:

A výsledok už bude konečne taký, aký sme chceli:

Teraz už len stačí premenovať hlavičky stĺpcov na také, aké tam majú byť:

A potom už len stačí stlačiť tlačítko “Zavrieť a načítať”, resp. “Zavrieť a použiť”, aby sa vám to načítalo do Excelu/PowerPivotu/Power BI, a môžete pekne krásne analyzovať aj tieto kontingenčné dáta, ktoré sú už odteraz normálnymi dátami 🙂

Toto bol síce komplikovanejší postup, ale nebolo to nič ťažké. Stačilo skombinovať iba zopár operácií Power Query, a ak už viete, ako na to, tak to celé trvalo do 1 minúty. Čo je oveľa ľahšie a rýchlejšie, ako pátrať v Exceli po skrytých funkciách, či nebodaj písať makrá. A takto rýchlo idú upraviť nielen kontingenčné dáta. A viete, koľko času vám vie Power Query ušetriť pri príprave nielen takýchto, ale aj oveľa horšie usporiadaných dátach? Veľa 🙂