Excelovské súbory sú snáď najčastejší zdroj dát pri všetkých analytických nástrojoch. Nie sú síce vhodné na univerzálny prenos dát, ale vo väčšine prípadov fungujú a poslúžia svojmu účelu. Až dokým nezačnete využívať všetky vlastnosti Excelu. V niektorých prípadoch je potom naimportovanie jednoduchého súboru cez Power Query, či už do Excelu, PowerPivotu alebo Power BI, neskutočne otravnou a zdĺhavou úlohou. Preto sa teraz pozrieme, ako sa s tým elegantne popasovať.

Povedzme, že potrebujeme do Power BI či PowerPivotu naimportovať takýto excelovský súbor s takouto tabuľkou:

Spravíme teda obligátny postup – otvoríme si Power Query, či už v Exceli, alebo v Power BI Desktope. V závislosti od nášho prostredia spravíme jeden z týchto krokov:

• 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 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 tú tabuľku. Po načítaní do dátového modelu to bez problémov prejde. Na tom nie je nič zaujímavé.

Skúsime potom ale naimportovať trochu väčší súbor – konkrétne tento súbor. Jeho začiatok je rovnaký ako v ukážke vyššie, ale tentokrát má súbor 2000 riadkov. Čo by sa na tom mohlo pokaziť? Vyskúšame.

Otvoríme teda Power Query podľa postupu vyššie, a keď sa otvorí Navigátor, tak v ňom vyberieme tú tabuľku s názvom “Tabuľka1”, a dáme ju načítať:

Tentokrát načítanie súboru nezbehne. Respektíve, v niektorých verziách Power Query zbehne s chybami, a inde nezbehne. Napr. v najnovšom Power BI Desktope síce zbehne, naimportuje všetkých 2000 riadkov, ale niekde je chyba:

V Exceli to zasa vyzerá takto – zobrazí sa iba nenápadné hlásenie v paneli dotazov napravo:

A my musíme zistiť kde, pretože inak riskujeme nesprávne vstupné údaje, a tým pádom aj nesprávne analýzy. Preto to musíme opraviť.

Najprv teda zistíme, kde je chyba. Ak ešte stále máte zobrazené to okno s chybou, tak kliknite na odkaz “Zobraziť chyby” v ňom (resp. ak ste v Exceli, tak kliknite na to hlásenie “Počet chýb: 2”). Ak ste ho už zatvorili, tak dajte aktualizovať tento dotaz, resp. stlačte tlačítko Obnoviť v hlavnom menu, a mali by ste dostať znova toto isté okno. Kde tiež kliknete na odkaz “Zobraziť chyby”:

To nám otvorí editor Power Query, kde nájdeme v paneli naľavo vytvorený ďalší dotaz, ktorého názov by mal začínať na “Chyby v dotazoch – …..”. Zároveň by vám malo zobraziť tie 2 riadky z posledného načítania dát, ktoré obsahujú chyby:

A tam vidíte, že v stĺpci “Cena bez DPH” je 2x pod sebou napísané “Error”. Tie 2 chyby sú teda v tomto stĺpci. A aby ste nemuseli hádať, v čom bol problém, tak si tie chyby vieme po jednom aj zobraziť. ALE POZOR – NEKLIKAJTE na ten nápis “Error”, ako by ste intuitívne predpokladali, pretože to síce chybu zobrazí, ale zároveň z nej spraví aj výsledok dotazu. Čo nechcete. Preto na to NEKLIKAJTE.

Namiesto toho kliknite myšou do toho prázdneho miesta v bunke s chybou. Keď do nej kliknete, tak sa naspodku okna zobrazí žlté hlásenie, v ktorom je zobrazená tá chyba:

A takto si viete postupne pozrieť chybu pre každú jednu bunku. V čom je ale problém?

Chybová hláška hovorí o tom, že sa “nepodarilo konvertovať na číslo”. O dva riadky nižšie je uvedená hodnota, ktorú sa nepodarilo skonvertovať: hodnota “dohodou”. Pri dvoch objednávkach je totižto namiesto ceny uvedené iba “dohodou”. Toto sa vám pri importe z databáz nemôže stať, ale pri Exceli je to celkom bežný úkaz. Pretože Excel umožňuje dať do jedného stĺpca čísla, dátumy aj reťazce – čiže čo len chcete – ale PowerPivot vyžaduje, aby v jednom stĺpci bol len jeden typ údajov. V tomto prípade si Power Query tiplo na základe prvých riadkov, že sú tam iba čísla, a teda všetky importované hodnoty sa snažilo pri načítavaní prekonvertovať na číslo, aj keď neboli číselné.

A toto nie je problém iba Power Query. Je to problém každej jednej aplikácie, ktorá sa snaží načítavať excelovské súbory cez klasický excelovský databázový ovládač. Čiže tento problém majú takmer všetky štandardné aplikácie na svete. A rieši sa to podobne, ako v tomto prípade.

Prvé riešenie – odstránenie chýb

Prvé riešenie spočíva v odstránení chýb, resp. ich nahradení nejakou predvolenou hodnotou. Pretože ak by sme to nespravili, tak Power Query to spraví automaticky pri načítavaní údajov, a zvyčajne nahradí chybné hodnoty prázdnou hodnotou. Čo nie vždy chcete.

Preto nahradíme chybné ceny objednávok nejakou predvolenou hodnotou, napr. hodnotou 100. Lebo sme sa teraz rozhodli, že tam chceme dať 100. Prejdeme teda najprv z tohto dotazu s chybnými riadkami, do pôvodného dotazu so všetkými riadkami. Kliknite teda v paneli naľavo na názov dotazu “Tabuľka1”. To nám zobrazí všetky objednávky:

Potom kliknite pravým tlačítkom myši na ZÁHLAVIE stĺpca “Cena bez DPH”, a v ponuke vyberte “Nahradiť chyby”:

Zobrazí sa okno nazvané “Nahradenie chýb”, kde zadajte hodnotu, ktorou sa majú nahradiť chybné – v tomto prípade nečíselné – hodnoty v stĺpci “Cena bez DPH”. My teda zadáme 100, a stlačíme OK:

To nám pridá ďalší krok do postupnosti operácií, a teraz stačí už iba dať v hlavnom menu “Zavrieť a načítať”, resp. “Zavrieť a použiť”, a údaje sa nám načítajú tentokrát bez chýb. Potvrdíme si to samozrejme aj odfiltrovaním výsledku v dátovom modeli len na tie riadky, kde je “Cena bez DPH” rovná 100:

Na konci ešte nezabudnite z Power Query odstrániť ten dotaz s chybnými riadkami, pretože po odstránení chyby ho už nepotrebujete, a zbytočne tam bude zavadzať. To by malo byť už potom všetko.

Druhé riešenie – načítanie všetkých údajov aj s chybami

Niekedy však chcete načítať aj takéto chybné údaje. A tam sa používajú 2 triky.

Prvý trik je trik ešte z dávnych čias, keď ešte Power technológie neexistovali. Riešenie je nájsť si v registroch jedno konkrétne nastavenie, ktoré hovorí o tom, že na základe koľkých prvých riadkov si má excelovský databázový ovládač tipnúť typ údajov v danom stĺpci. Štandardne je to nastavené tuším na 16, ale užívateľ to môže zvyčajne zmeniť na iné číslo. Nie je to však preferovaná metóda, pretože úpravou registrov môžete toho oveľa viac pokaziť. A vo väčších organizáciách sa k tomu bežný užívateľ preto ani nedostane. Nehovoriac o nechválne známej funkcionalite Windows 10, ktoré pri väčších aktualizáciách systému mení veľa nastavení v registroch naspäť na predvolené hodnoty. Preto to nie je úplne spoľahlivé riešenie.

Druhý trik je v tom, že takéto stĺpce, kde sú kadejaké hodnoty, naimportujeme v Power Query ako obyčajné reťazce. Pretože to je jediný typ údajov v dátovom modeli, kde môže byť v jednom stĺpci uložené hocičo. Síce sa s tým nebude pracovať zrovna jednoducho, ale ak to tam chcete mať, tak toto je najjednoduchšia možnosť.

Začneme teda podobne ako v prvom riešení tým, že si najprv otvoríme Editor Power Query, cez to chybové hlásenie “Zobraziť chyby”, resp. “Počet chýb: 2”:

Dostaneme sa teda opäť do dotazu s chybnými riadkami:

Prejdeme potom z tohto dotazu s chybnými riadkami, do pôvodného dotazu so všetkými riadkami. Kliknite teda v paneli naľavo na názov dotazu “Tabuľka1”. To nám zobrazí všetky objednávky:

Potom choďte do panelu napravo, kde sa nachádzajú “Použité kroky”. Tam si všimnite krok s názvom “Zmenený typ”. Ak tam máte za ním aj iné kroky, tak kliknite na tento krok:

Potom choďte do stĺpca “Cena bez DPH”, a v jeho záhlaví kliknite na ikonku “1.2” v jeho ľavom hornom rohu. To je ikonka na zmenu typu údajov daného stĺpca. V ponuke potom vyberte typ údajov “Text”:

Vyskočí potom na vás takéto okno, kde vyberte možnosť “Nahradiť aktuálnu”:

Stĺpec sa potom zmení na textový stĺpec:

Teraz stačí už iba dať v hlavnom menu “Zavrieť a načítať”, resp. “Zavrieť a použiť”, a údaje sa nám načítajú ako text aj do dátového modelu:

A keď si tie riadky dáme odfiltrovať iba na tie 2 objednávky, kde predtým bola chyba, tak uvidíte, že tentokrát sa nám naimportovala aj hodnota “dohodou”:

Nie je to síce ideálny stav, ale je využiteľný napr. pri analýze chybných dát. Prípadne na čokoľvek iné to potrebujete.

Na konci ešte nezabudnite z Power Query odstrániť ten dotaz s chybnými riadkami, pretože po odstránení chyby ho už nepotrebujete, a zbytočne tam bude zavadzať. To by malo byť už potom všetko.

---

Takto teda viete naimportovať každý jeden excelovský súbor, či už s aj s chybami, alebo s opravenými hodnotami. Finálny výber riešenia už nechám na vás, pretože vy sami viete, čo s tým ďalej chcete robiť. Ukázali sme si, ako to ide naimportovať, a zvyšok zábavy už ponechám vám 🙂