Spracovanie chýb
Podobne ako majú IFERROR Excel a jazyk DAX funkciu, Power Query má svoju vlastnú syntax na testovanie a zachytenie chýb.
Ako je uvedené v článku o riešení chýb v doplnku Power Query, chyby sa môžu zobraziť buď na úrovni kroku, alebo v bunke. Tento článok sa zameriava na to, ako môžete chytiť a spravovať chyby na základe vlastnej špecifickej logiky.
Poznámka
Na znázornenie tohto konceptu tento článok používa ako zdroj údajov zošit programu Excel. Predstavované koncepty sa vzťahujú na všetky hodnoty v Power Query a nielen tie, ktoré pochádzajú z excelového zošita.
Vzorovým zdrojom údajov pre túto ukážku je excelový zošit s nasledujúcou tabuľkou.
Táto tabuľka z excelového zošita obsahuje excelové chyby, ako napríklad #NULL!, #REF!, a #DIV/0! v stĺpci Standard Rate (Štandardná rýchlosť ). Keď importujete túto tabuľku do editora Power Query, nasledujúci obrázok znázorňuje, ako vyzerá.
Všimnite si, ako sa chyby z excelového zošita zobrazujú s hodnotou [Error] v jednotlivých bunkách.
V tomto článku sa dozviete, ako nahradiť chybu inou hodnotou. Okrem toho sa tiež naučíte, ako chytiť chybu a použiť ju pre svoju vlastnú špecifickú logiku.
Poskytnutie alternatívnej hodnoty pri hľadaní chýb
V tomto prípade je cieľom vytvoriť nový stĺpec Final Rate (Konečná sadzba ) vo vzorovom zdroji údajov, ktorý používa hodnoty zo stĺpca Standard Rate (Štandardná sadzba ). Ak sa vyskytnú nejaké chyby, použije hodnotu zo zodpovedajúceho stĺpca Special Rate (Špeciálna sadzba ).
Ak chcete vytvoriť nový vlastný stĺpec, prejdite do ponuky Pridať stĺpec a vyberte položku Vlastný stĺpec. V okne Vlastný stĺpec zadajte vzorec try [Standard Rate] otherwise [Special Rate]. Pomenujte tento nový stĺpec Final Rate (Konečná sadzba).
Tento vzorec sa pokúsi vyhodnotiť stĺpec Standard Rate (Štandardná rýchlosť) a jeho hodnota bude výstupom, ak sa nenájdu žiadne chyby. Ak sa v stĺpci Standard Rate (Štandardná sadzba ) nájdu chyby, výstupom je hodnota definovaná po otherwise príkaze, čo je v tomto prípade stĺpec Special Rate (Špeciálna sadzba ).
Po pridaní správnych typov údajov do všetkých stĺpcov v tabuľke, nasledujúci obrázok znázorňuje, ako vyzerá finálna tabuľka.
Poznámka
Ako alternatívny prístup môžete zadať aj vzorec try [Standard Rate] catch ()=> [Special Rate], ktorý je ekvivalentom predchádzajúceho vzorcu, ale pomocou kľúčového slova catch (úlovok) s funkciou, ktorá nevyžaduje žiadne parametre.
Kľúčové catch slovo bolo predstavené v doplnku Power Query v máji 2022.
Zadanie vlastnej logiky podmienenej chyby
Použitie rovnakého vzorového zdroja údajov ako je predchádzajúca časť, novým cieľom je vytvoriť nový stĺpec pre Final Rate (Konečná sadzba). Ak hodnota štandardnej sadzby existuje, použije sa táto hodnota. V opačnom prípade sa použije hodnota zo stĺpca Special Rate (Špeciálna sadzba ), s výnimkou riadkov s ľubovoľnou #REF! chybou.
Poznámka
Výhradný účel vylúčenia #REF! chyby je na účely demonštrácie. Vďaka konceptom uvedeným v tomto článku môžete do záznamu chýb zacieliť všetky polia podľa svojho výberu.
Keď vyberiete prázdny znak vedľa chybovej hodnoty, zobrazí sa tabla s podrobnosťami v dolnej časti obrazovky. Tabla podrobností obsahuje dôvod DataFormat.Errorchyby aj chybové hlásenie: Invalid cell value '#REF!'
Naraz môžete vybrať len jednu bunku, takže môžete efektívne preskúmať iba komponenty chyby jednej chybovej hodnoty. Práve v tomto bode vytvoríte nový vlastný stĺpec a použijete try výraz.
Použitie try s vlastnou logikou
Ak chcete vytvoriť nový vlastný stĺpec, prejdite do ponuky Pridať stĺpec a vyberte položku Vlastný stĺpec. V okne Vlastný stĺpec zadajte vzorec try [Standard Rate]. Dajte tomuto novému stĺpcu názov Všetky chyby.
Výraz try konvertuje hodnoty a chyby na hodnotu záznamu, ktorá označuje, či try sa s výrazom zaobchádzalo alebo nie, a na správnu hodnotu alebo záznam chyby.
Tento novovytvorený stĺpec môžete rozbaliť pomocou hodnôt záznamu a pozrieť sa na dostupné polia, ktoré sa majú rozbaliť, výberom ikony vedľa hlavičky stĺpca.
Táto operácia sprístupňuje tri nové polia:
- All Errors.HasError – zobrazuje, či hodnota zo stĺpca Standard Rate (Štandardná rýchlosť ) vyskytla chybu alebo nie.
- All Errors.Value – ak hodnota zo stĺpca Standard Rate (Štandardná sadzba ) nemala žiadnu chybu, tento stĺpec zobrazí hodnotu zo stĺpca Standard Rate (Štandardná sadzba ). Pre hodnoty s chybami toto pole nie je k dispozícii a počas operácie rozbalenia má
nulltento stĺpec hodnoty. - All Errors.Error – ak hodnota zo stĺpca Standard Rate (Štandardná sadzba ) mala chybu, tento stĺpec zobrazí záznam chyby pre hodnotu stĺpca Standard Rate (Štandardná sadzba ). Pre hodnoty bez chýb nie je toto pole k dispozícii a počas operácie rozbalenia má
nulltento stĺpec hodnoty.
Na ďalšie skúmanie môžete rozbaliť stĺpec All Errors.Error a získať tri súčasti záznamu chyby:
- Dôvod chyby
- Chybové hlásenie
- Podrobnosti o chybe
Po vykonaní operácie rozbalenia sa v poli All Errors.Error.Message zobrazí konkrétne chybové hlásenie, ktoré vám povie, aká chyba v Exceli sa vyskytla v každej bunke. Chybové hlásenie je odvodené od poľa Chybové hlásenie záznamu chyby.
Teraz, keď sa v novom stĺpci zobrazí každé chybové hlásenie, môžete vytvoriť nový podmienený stĺpec s názvom Final Rate (Konečná sadzba ) a nasledujúcimi klauzulami:
- Ak sa hodnota v stĺpci All Errors.Errors.Message rovná
null, výstupom je hodnota zo stĺpca Standard Rate (Štandardná sadzba ). - Inak, ak sa hodnota v stĺpci All Errors.Errors.Message nerovná
Invalid cell value '#REF!'., výstupom je hodnota zo stĺpca Špeciálna sadzba . - Inak, hodnota null.
Po zachovaní iba stĺpcov Account (Konto), Standard Rate (Štandardná sadzba), Special Rate (Špeciálna sadzba) a Final Rate (Konečná sadzba) a pridaní správneho typu údajov pre každý stĺpec, nasledujúci obrázok znázorňuje, ako vyzerá finálna tabuľka.
Použitie try vlastnej logiky a catch s vlastnou logikou
Prípadne môžete vytvoriť nový vlastný stĺpec aj pomocou try kľúčových slov a catch .
try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null
