Power Query v Exceli aj v Power BI viete využiť na načítanie dát z čohokoľvek. Čo však v prípade, že máte ako zdroj dát excelovský súbor, ktorý má dáta rozdelené do viacerých záložiek? A navyše, ak sa ten počet záložiek mení v každom súbore, a neviete dopredu, koľko ich je? Aj v tomto prípade vás zachráni Power Query.

Povedzme, že máme takýto excelovský súbor, kde sú výpisy hovorov, rozdelené do 3 záložiek, resp. hárkov Excelu:

Tieto 3 tabuľky sú na takýchto hárkoch/záložkách v Exceli:

A čo keď teraz potrebujete načítať zoznam hovorov zo všetkých 3 záložiek do 1 tabuľky, aby ste ich mohli analyzovať v kontingenčke? Navyše keď dopredu neviete, či tých záložiek nabudúce bude 5, 8 alebo 50? A aby ste to nemuseli vždy robiť manuálne tak, že skopírujete ručne každú tabuľku do osobitného Excelu a tam to zlepíte do 1 tabuľky?

Našťastie máme Power Query, v ktorom je to hračka, ak už viete, ako na to.

V prvom kroku sa uistite, že dáta sú uložené v súbore vo formáte XLSX. Ak máte iba formát XLS, tak pred tým, ako budete pokračovať, si dajte tento súbor uložiť ako XLSX. Dôvod je ten, že keď budete používať starý excelovský formát s príponou XLS, tak sa vám načíta iba niekoľko prvých stĺpcov. Preto si takéto súbory najprv otvorte, a dajte uložiť vo formáte XLSX.

Potom si otvorte Power Query, či už v Exceli, alebo v Power BI Desktope. 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, alebo Excel z Office 365, 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.

Otvorí sa vám okno, v ktorom vyberte svoj súbor, ktorý chcete naimportovať. Po potvrdení sa zobrazí okno Navigátora, kde v našom prípade uvidíme tie 3 záložky ako osobitné tabuľky, vo výbere naľavo:

Tam kliknite na názov prvej tabuľky, a potom na tlačítko Upraviť:

Otvorí sa Editor Power Query aj s náhľadom dát. V paneli napravo, nazvanom POUŽITÉ KROKY, zmažte všetky kroky okrem prvého, pomocou toho symbolu X vedľa každého z nich:

Mal by vám tam zostať iba krok nazvaný Zdroj, a náhľad dát by mal vyzerať teraz takto:

V tomto náhľade dát nás teraz bude zaujímať stĺpec nazvaný Data, kde sa nachádzajú podtabuľky, reprezentujúce každý jeden hárok v danom excelovskom zošite. Tie potrebujeme rozbaliť na jednotlivé záznamy, pre každú jednu z tých podtabuliek (čiže pre každý jeden hárok). To spravíme tak, že v pravom hornom rohu v stĺpci Data klikneme na tú rozbočujúcu sa ikonku:

Otvorí sa malé menu, v ktorom zrušte začiarknutie pri políčku “Použiť ako predponu pôvodný názov stĺpca”, a stlačte OK:

To vám rozbalí všetky tabuľky zo všetkých hárkov do jednej súvislej tabuľky:

Ak však tie tabuľky neboli predtým označené v Exceli ako pomenované tabuľky (cez klávesovú skratku Ctrl+T), tak budú potrebné ešte 3 kroky. V prvom kroku potrebujeme z prvého riadku dát spraviť hlavičky tabuľky. To spravíme tak, že v hlavnom menu klikneme na záložku Transformovať, a v nej na tlačítko “Použiť prvý riadok ako hlavičky”:

Po vykonaní tejto operácie to bude vyzerať takto:

Vidíte ale, že to posunulo len prvý riadok do hlavičiek, ale nie ďalšie hlavičky na riadkoch nižšie. To ošetríme zachvíľu.

Power Query nám ale prihodilo do zoznamu operácii napravo, operáciu “Zmenený typ”, ktorá môže neskôr robiť problémy. Preto ju zmažeme tým X-kom vedľa nej:

…a výsledný zoznam operácií bude vyzerať takto:

A teraz potrebujeme ošetriť to, že pôvodné hlavičky tabuliek z jednotlivých hárkov Excelu sú ešte stále medzi riadkami s dátami. To ošetríme jednoducho autofiltrom – napr. tak, že dáme odfiltrovať preč všetky riadky, kde v stĺpci “Dátum a čas” sa nenachádza text “Dátum a čas”. A to spravíme tak, že klikneme v hlavičke toho stĺpca na ikonku so šípočkou, a v menu vyberieme položku Filtre => Nerovná sa:

V ďalšom okne napíšeme do políčka vpravo celý ten názov stĺpca, z ktorého sme to otvorili. V našom prípade je to “Dátum a čas”. Potom klikneme na tlačítko OK:

Výsledok bude vyzerať takto:

Teraz už vidíte, že tie riadky s hlavičkami zmizli.

V poslednom kroku potrebujeme zadefinovať každému stĺpcu správny dátový typ, aby sa nám to nenaimportovalo do Excelu, resp. Power BI, ako text. To spravíte tak, že v ľavom hornom rohu každého stĺpca nájdete ikonku, na ktorú keď kliknete, tak sa zobrazí menu s ponukou dátových typov:

V našom prípade zmeníme dátový typ pre jednotlivé stĺpce takto:

• pre stĺpec “Dátum a čas” na typ “Dátum a čas”,
• pre stĺpec “Telefón” na typ “Text”,
• pre stĺpec “Trvanie” na typ “Čas”,
• pre stĺpec “Typ hovoru” na typ “Text”.

Takto to viete spraviť buď po jednom, alebo označením viacerých stĺpcov naraz a kliknutím na tlačítko “Typ údajov: ….” v hlavnom menu na záložke Domov, ak to chcete meniť hromadne. Ak to máte hotové, tak napr. v našom prípade to bude vyzerať takto:

Posledné 3 stĺpce môžete zmazať, pretože nebudú potrebné (označiť ako v Exceli, a potom pravý klik => Odstrániť stĺpce). Prípadne môžete odstrániť aj prvý stĺpec, pretože ho asi nebudete potrebovať – je v ňom napísané, z ktorého hárku/záložky Excelu bol daný riadok naimportovaný.

Keď to máte dokončené, tak v hlavnom menu choďte na záložku Domov, a stlačte tlačítko “Zavrieť a načítať”, resp. “Zavrieť a použiť”:

…a tadáááá, máte načítané všetky záložky z pôvodného excelovského súboru naraz v jednej tabuľke:

Výhodou tejto metódy je to, že je zajedno, v koľkých záložkách v Exceli ste mali všetky záznamy. Tento postup zaistí načítanie všetkých záložiek v Exceli, a ich zlúčenie do jednej tabuľky. To vás odbremení od nudného kopírovania tabuliek jednej po druhej každý jeden týždeň či mesiac do osobitného súboru, a namiesto toho to budete mať celé hotové za pár sekúnd. A kvôli tomu sa to oplatí aspoň vyskúšať, no nie? 🙂