Power Query už dlhšie trpí jedným bugom, ktorý stále nie je opravený. V niektorých prípadoch príkaz try nezafunguje, keď sa vyskytne chyba, a namiesto odchytenia chyby túto chybu vyvolá. Čo je tak trochu mimo s ohľadom na to, na čo slúži príkaz try. A keďže to potrebujeme rozchodiť, tak sa na to pozrieme, prečo sa to deje, a ako z toho von.

Príkaz try v Power Query, resp. v jazyku M, slúži na to, aby sme vedeli odchytiť chybu, a prípadne ju potom spracovať. Napríklad vykonať niečo iné namiesto tej chyby, čo ju buď opraví, alebo vykoná nejakú alternatívnu akciu. Čo však, keď to niekedy nefunguje, a spadne to do kategórie „nebude to opravené asi ani za 10 rokov“?

Ukážeme si to na príkladoch. Najprv vtedy, keď to funguje (čo je drvivá väčšina prípadov), a potom keď to nefunguje.

Vo všetkých prípadoch budeme pracovať v Rozšírenom editore Power Query, priamo s M skriptom. Ak ste s ním ešte nepracovali, tak si ukážeme, ako otvoriť prázdny dotaz v Power Query, či už v Exceli, alebo v Power BI Desktope. A potom ako otvoriť Rozšírený editor, kde nájdete M skript k danému dotazu. Doňho potom napíšeme skripty nižšie.

Ako vytvoriť prázdny dotaz v Power Query

Otvorte si nový dotaz v Power Query typu “Prázdny dotaz”, resp. dajte si načítať dáta z dátového zdroja typu “Prázdny dotaz”.V závislosti od toho, s ktorým Power Query pracujete, sa to robí 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 Z iných zdrojov => Prázdny dotaz,
• ak máte Excel 2016, tak v hlavnom menu choďte na záložku Údaje, a tam kliknite na Nový dotaz => Z iných zdrojov => Prázdny dotaz,
• 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 => Z iných zdrojov => Prázdny dotaz,
• ak máte Power BI Desktop, tak v hlavnom menu kliknite na Načítať údaje => Prázdny dotaz (úplne na konci zoznamu so všetkými dátovými zdrojmi).

Následne sa vám otvorí Power Query, a v ňom kliknite v hlavnom menu na tlačítko “Rozšírený editor”:

Potom sa otvorí takéto okno:

V ňom zmažte všetok text, a namiesto neho tam budeme písať vlastný skript. Zadajte tam na začiatok takýto skript:

let 
   Zdroj = 1
in 
   Zdroj

Potom stlačte tlačítko Hotovo:

V Power Query sa vytvorí nový dotaz z tohto M skriptu, ktorý vám niečo vygeneruje. V tomto prípade to zobrazí takýto výsledok:

Od tohto momentu už viete, kam máte zadávať M skript, a budeme teda pokračovať v pôvodnej téme článku.

Keď príkaz try funguje

Povedzme, že chceme ošetriť delenie nulou. Alebo nejaký iný prípad, keď môže vzniknúť chyba, a my chceme zabezpečiť, aby náš M skript vždy dobehol bez chyby. Chybu si teda vytvoríme týmto skriptom:

let 
    cislo = 1,
    retazec = "test",
    vysledok = cislo + retazec
in 
    vysledok

Tento skript sa pokúsi spočítať číslo 1 s reťazcom „test“. A spadne s takouto chybou:

Chybu ošetríme tak, že použijeme príkaz try … otherwise. Je to alternatíva príkazu try … catch z objektových programovacích jazykov, ako napr. C#, Java či PHP. Jeho syntax je:

try príkaz1 otherwise alternatívnyPríkaz

Príkaz try funguje tak, že najprv sa pokúsi spustiť príkaz1. Ak sa ho podarí spustiť bez chyby, tak vráti jeho výsledok. Ak sa však počas spúšťania príkazu1 vyskytne chyba, tak tú chybu potlačí, pokúsi sa spustiť alternatívnyPríkaz, uvedený za slovíčkom „otherwise“. A ak to spadne aj v tomto príkaze, tak smola…alebo to zabaľte celé do ďalšieho príkazu try. Je to príkaz ako každý iný.

V našom prípade skript ošetríme tak, že ak to nejde spočítať, tak vrátime nulu:

let 
    cislo = 1,
    retazec = "test",
    vysledok = try cislo + retazec otherwise 0
in 
    vysledok

Teraz už M skript zbehne, a vráti výsledok 0:

Takže takto v skratke funguje základná syntax pre príkaz try.

Keď príkaz try nefunguje

Existujú však aj prípady, keď sa síce vyskytne chyba, ale príkaz try ju neodchytí. Ukážeme si to na trochu zložitejšom príklade, kde chceme načítať tabuľku z SQL Servera. A ak taká tabuľka v databáze na serveri neexistuje, tak chceme vrátiť prázdnu tabuľku s rovnakou štruktúrou, akú by sme načítali z databázy. Aby sme potom mohli pokračovať v spracovávaní a transformáciách tabuľky v Power Query.

Spustíme teda takýto M skript:

let
    Zdroj = Sql.Database("nasServer", "nasaDatabaza", [Query="SELECT * FROM DimCurrency", CreateNavigationProperties=false])
in
    Zdroj

V našom prípade vráti tento dotaz takúto tabuľku:

Čo však, ak takáto tabuľka v danej databáze neexistuje? V tom prípade Power Query samozrejme nenačíta žiadne údaje, a skončí napr. s takouto chybou:

Poviete si – žiaden problém, veď sme vyzbrojení, a máme na to príkaz try. Prepíšeme teda skript takto:

let
    Zdroj = Sql.Database("nasServer", "nasaDatabaza", [Query="SELECT * FROM DimCurrency", CreateNavigationProperties=false]),
    AlternativnyZdroj = #table(type table [CurrencyKey=Int64.Type,CurrencyAlternateKey=text,CurrencyName=text], {}),
    Vysledok = try Zdroj otherwise AlternativnyZdroj
in
    Vysledok

Na prvý pohľad všetko fajn. Prvý riadok skriptu zadefinuje pôvodný zdroj, druhý riadok alternatívny zdroj – prázdnu tabuľku s rovnakými stĺpcami aké očakávame z databázy – a tretí riadok vyskúša vyhodnotiť pôvodný zdroj, a v prípade chyby by mal vrátiť alternatívny zdroj. To sa však nestane, a skript skončí s rovnakou chybou.

Nepomôže ani masochistické zabalenie do jedného aliasu, pretože v tom chyba nebola:

let
    Zdroj =
      try 
        Sql.Database("nasServer", "nasaDatabaza", [Query="SELECT * FROM DimCurrency2", CreateNavigationProperties=false])
    
      otherwise 
         #table(type table [CurrencyKey=Int64.Type,CurrencyAlternateKey=text,CurrencyName=text], {})
    
in
    Zdroj

A kde je vlastne chyba?

Prečo to v príklade predtým fungovalo, a teraz to v podstate v rovnakom prípade nefunguje? Lebo Power Query občas funguje inak.

Keď som to niekedy dávno riešil prvýkrát, tak som zistil, že príkaz try má svoju návratovú „hodnotu“. V prípade chyby vráti chybový záznam, a v opačnom prípade výsledok príkazu, ktorý sme skúšali spustiť príkazom try. V oboch prípadoch je ten výsledok vrátený ako zoznam, kde sa nachádza aj príznak chyby, plus vnorený výsledok, zabalený do druhej položky.

Skúsime teda teraz spustiť iba príkaz try a zobraziť jeho výsledok:

let
    Zdroj =
      try 
        Sql.Database("nasServer", "nasaDatabaza", [Query="SELECT * FROM DimCurrency2", CreateNavigationProperties=false])
in
    Zdroj

Výsledok bude vyzerať takto:

Podľa ikonky pri dotaze vidno, že je to zoznam. Kde prvá položka je nazvaná „HasError“ a obsahuje logickú hodnotu, či sa vyskytla alebo nevyskytla chyba, a druhá položka „Value“ obsahuje výsledok tej operácie. Zvyčajne buď „Error“ (kde sa nachádzajú všetky informácie o chybe), alebo „Table“ (kde sa nachádza tabuľka s výslednými údajmi).

V tomto prípade ale vidíte, že príznak HasError nie je nastavený, napriek tomu, že sa vyskytla chyba. A teraz babo raď. Žeby ďalší „to nie je bug, ale feature“ ?

V podstate je to tak. Power Query totiž používa „lenivé“ vyhodnocovanie M skriptu, a niektoré z príkazov vyhodnotí až vtedy, keď ich je potrebné vyhodnotiť. A tie, ktoré nie je potrebné vyhodnocovať, tak tie nespustí vôbec. A dokonca aj k dátovým zdrojov – k databázam, súborom a pod. – sa pripojí nie na tom riadku, kde je uvedený daný „príkaz“ – ale až keď to naozaj treba. Pretože v skutočnosti tie riadky nie sú príkazy, ale aliasy. Ktoré sa vyhodnotia podľa potreby až v klauzule „in“ na konci skriptu. Viac o tom v pokročilom kurze Power Query, alebo Power BI.

V našom prípade sme si zadefinovali príkaz/alias Zdroj na začiatku skriptu, ale v tom momente ešte nebolo potrebné ho vyhodnocovať. Tak si Power Query povedalo, že eném tam nie je chyba, a tomu aliasu Zdroj nastavilo, že tam chyba nebola. A potom, keď sa spustilo vyhodnotenie M skriptu v klauzule „in“, tak už bolo potrebné ten príkaz „Sql.Database“ spustiť. Tak ho teda spustil. Alias Zdroj však už mal nastavený príznak HasError na false, a spustenie akéhokoľvek oneskoreného príkazu v rámci príkazu try to už nemení. Divné, ale je to tak. Je to popísané aj na oficiálnom fóre Power BI. Čo však s tým?

Ako to opraviť

Vrátime sa teda k nášmu pôvodnému skriptu:

let
    Zdroj = Sql.Database("nasServer", "nasaDatabaza", [Query="SELECT * FROM DimCurrency", CreateNavigationProperties=false]),
    AlternativnyZdroj = #table(type table [CurrencyKey=Int64.Type,CurrencyAlternateKey=text,CurrencyName=text], {}),
    Vysledok = try Zdroj otherwise AlternativnyZdroj
in
    Vysledok

Riešením je v tomto prípade vynútiť vyhodnotenie príkazu vo vnútri príkazu try už v tom aliase, kde sa vyskytuje. Alebo čím najskôr. Pretože potom Power Query správne nastaví príznak HasError, a správne spracuje prípadnú chybu v klauzule otherwise.

Skript upravíme takto:

let
    Zdroj = Sql.Database("nasServer", "nasaDatabaza", [Query="SELECT * FROM DimCurrency", CreateNavigationProperties=false]),
    ZdrojTest = try Table.RowCount(Zdroj),
    AlternativnyZdroj = #table(type table [CurrencyKey=Int64.Type,CurrencyAlternateKey=text,CurrencyName=text], {}),
    Vysledok = if ZdrojTest[HasError] then AlternativnyZdroj else Zdroj
in
    Vysledok

V tomto prípade trik spočíva v tom, že zavoláme funkciu Table.RowCount (vráti počet riadkov v zadanej tabuľke) nad aliasom Zdroj. Tým pádom vynútime vyhodnotenie aliasu Zdroj, pretože tam už Power Query nemôže odložiť volanie databázového dotazu na neskôr, a musí ho spustiť. Výsledok funkcie Table.RowCount uložíme do aliasu ZdrojTest. Ktorý už bude mať správne nastavený príznak HasError.

Na konci potom už len v aliase Vysledok otestujeme, či sa pri hodnotení aliasu ZdrojTest vyskytla chyba. Ak áno, tak vrátime AlternativnyZdroj, inak vrátime pôvodný výsledok z aliasu Zdroj.

Výsledok potom pri nedostupnej databáze, neexistujúcej tabuľke alebo akejkoľvek inej chybe bude vyzerať takto:

A ak taká tabuľka v databáze je, a podarí sa ju úspešne načítať, tak to vráti túto tabuľku:

A záhada je vyriešená, a report opäť funguje 🙂

---

Takto teda viete rozchodiť príkaz try, aj keď nefunguje. Princíp spočíva v tom, že vynútime vyhodnotenie skúšaného príkazu nejakou inou, ideálne nejakou základnou funkciou jazyka M. To, ktoré príkazy sa vyhodnocujú hneď a ktoré neskôr, resp. „lenivým“ spôsobom, asi nikde nenájdete. Trpia tým však viac-menej všetky funkcie na načítavanie údajov z dátových zdrojov – či už z databáz, z excelovských a textových súborov, z webových služieb, a podobne. Takže ak narazíte na to, že vám príkaz try nefunguje pri načítavaní údajov, tak už teraz viete, prečo to je, a najmä, ako to spojazdniť. A trhanie vlasov si môžete odložiť na neskôr 🙂