Transformační příkazy v Power Query  – Datové transformace v Power BI II.

Často se setkáváme se situací, kdy data, která chceme použít v Power BI, musíme nejprve upravit a vyčistit. K tomu slouží nástroj Power Query. V minulém díle naší minisérie jsme si přiblížili jeho uživatelské rozhraní. Dnes se zaměříme na transformace dostupné přímo z tohoto rozhraní a ukážeme si práci s M kódem.

Začneme představením příkazů, které využijeme pro rozdělení, transformaci a přidávání sloupců.

Rozdělení sloupce

Příkaz 'Rozdělit sloupec' umožňuje si vybrat, na základě čeho budou data v daném sloupci rozdělena – například podle oddělovače, počtu znaků, pozice atd. Vzniklá data mohou být buď přepsána do nového sloupce/sloupců, nebo sloučena do nových řádků. Po rozdělení původní sloupec zanikne a je nahrazený novým sloupcem či sloupci. Proto je vhodné, chceme-li si původní data ponechat, nejprve tento sloupec duplikovat a až poté aplikovat příkaz k rozdělení.

Příkaz Rozdělit sloupec v Power Query

Textové transformace

'Formát a Extrahovat' využíváme na práci s textovými daty. Na rozdíl od příkazu 'Rozdělit sloupec' při použití "Formát" nebo "Extrahovat" zůstává původní sloupec nezměněn. 'Formát' umožňuje převést celý text na velká či malá písmena, převést každé první písmeno ve slově na velké, oříznout text nebo jej vyčistit, či přidat předponu nebo příponu. Příkaz 'Text ořezat' odstraňuje všechny mezery na začátku a konci textu, zatímco příkaz 'Vyčistit' odstraňuje z textu všechny ovládací znaky (Control characters) jako například začátek hlavičky textu, taby atd.

'Extrahovat' umožňuje oddělit část informace na základě počtu znaků, text před / za / mezi oddělovači. Pokud se v textu objevuje stejný oddělovač vícekrát, můžeme zvážit použití příkazu 'Rozdělit sloupec'.

Příklady použití příkazu Formát v Power Query

Příklady použití Extrahovat v Power Query

Numerické transformace

Další velkou skupinou transformací jsou 'Statistické, Standardní, Vědecké, Trigonometrické, Zaokrouhlovací a Informační příkazy' pro manipulaci s numerickými hodnotami v číselných sloupcích. Stejné příkazy najdeme na kartě 'Transformovat', stejně jako na kartě 'Přidat sloupec', přičemž pokrývají velkou část standardních matematických operací. Pokud zvolíme příkazy na kartě 'Transformovat', původní hodnota nezůstane zachovaná. Pokud použijeme příkazy na kartě Přidat sloupec, výpočet se provede jako nový sloupec.

Statistické, Standardní, Vědecké, Trigonometrické, Zaokrouhlovací a Informační příkazy v Power Query

Časové transformace

Příkazy transformací 'Datum, Čas a Trvání' dostupné na kartě Transformace, jsou aplikovatelné na sloupce nastavené s datovým typem: Datum a čas, Datum, Čas, Datum a čas, Časové pásmo či Trvání. S těmito funkcemi jsme schopni například stanovit začátek nebo konec měsíce, do kterého daný datum patří, nebo převést trvání z hodin na minuty.

Příkazy transformací Datumu, Času a Trvání

Příkaz Nahradit hodnoty a přidání sloupců

'Nahradit hodnoty' je další z transformačních příkazů, který můžeme aplikovat na sloupec, přičemž vybíráme, jestli chceme nahradit hodnoty nebo chyby. Nahrazení hodnot využijeme spíše k tvrdým opravám dat, zatímco příkaz nahrazení chyb může zabránit tomu, aby se kvůli výskytu chyby pozastavila plánovaná obnova dat.

Nahradit hodnoty v Power Query

TIP: Složitější nahrazovaní hodnot můžeme udělat v jazyku M. Příkladem je nahrazení hodnot hodnotami z jiného sloupce dole:

let

Source = #table({"Organizer", "Who Will Pay"}, {{"Petr", "dolphin"}, {"Tomáš", null}, {"Kristina", "dolphin"}, {"Leoš", null}}),

#"Replaced Payment" = Table.ReplaceValue(Source, each [Who Will Pay], each if [Who Will Pay] = null then [Organizer] else [Who Will Pay], Replacer.ReplaceValue, {"Who Will Pay"})

in

#"Replaced Payment"

Nyní se zaměříme na kartu 'Přidat sloupec' a možnosti tvorby nových sloupců. Nový sloupec můžeme vytvořit z příkazů: Vlastní sloupec, Podmíněný sloupec anebo Sloupec indexu.

Vytvoření Vlastního sloupce nám otevře dialogové okno s nabídkou existujících sloupců.  Samotný syntax Příkazu na vytvoření sloupce píšeme v jazyku M.

Přidání Vlastního sloupce

Další způsob přidání sloupce je Podmínkový sloupec. Jak vyplývá z názvu, umožňuje v dialogovém okně nastavit podmínky buď – anebo a definovat nové hodnoty v sloupci na základě těchto podmínek. Komplexnější podmínkovou logiku potom můžeme napsat také v jazyku M.

Přidání Podmínkového sloupce

Nakonec si ukážeme přidání indexového sloupce. Sloupec indexu nám přidává ke každému řádku číslo v číselné řadě, přičemž počáteční hodnotou může být 0, 1 s přírůstkem 1 anebo můžeme definovat vlastní počáteční hodnotu číselné řady a vlastní hodnotu přírůstku. Indexovaný sloupec nám pomáhá spravovat původní anebo seřazené pořadí dat ve sloupci.

Přidání indexu

Vytvářet sloupec v rozhraní Power Query ale není jedinou možností. Nový sloupec lze vytvořit v Power BI i pomocí jazyka DAX. V dalším díle naší minisérie Datové transformace v Power BI si porovnáme vytváření nového sloupce v Power BI Desktopu pomocí jazyka DAX s vytvářením sloupce v Power Query.

Závěr:

V tomto článku jsme si popsali některé dostupné příkazy v Power Query pro transformaci textových, numerických a časových hodnot. Jak vidíme, prostředí poskytuje širokou škálu příkazů a umožňuje využít jazyk M pro nastavení komplexní logiky. Dále jsme si ukázali, jak vytvořit nové vlastní, podmínkové a indexové sloupce, a také jak nahradit hodnoty a chyby v sloupci, abychom se vyhnuli potenciálním problémům s pravidelným obnovením dat.

Doufáme, že vám popis datových transformací pomohl lépe porozumět možnostem, které máte k dispozici. Jeden článek ale na celý popis datových transformaci nestačí. Pokud vás zajímá, jak upravovat data nejen na úrovni sloupce, ale i na úrovni dotazu, počkejte na další díl minisérie.

Zdroje:

Extract Parts of a Text Value in Power BI using a Delimiter: Power Query Transformation: Extract Parts of a Text Value in Power BI using a Delimiter: Power Query Transformation - RADACAD

Quick tour of the Power Query M formula language: Quick tour - PowerQuery M | Microsoft Docs

Split columns by delimiter: Split columns by delimiter - Power Query | Microsoft Docs

Trim Vs. Clean in Power BI and Power Query: Trim Vs. Clean in Power BI and Power Query - RADACAD