Power query je velmi výkonný nástroj pro extrakci a transformaci dat, který je součástí aplikace Excel 2016 (nebo novější), Excelu pro Office 365 a Power BI.
Najdete ho na kartě Data v části Získat & Transformovat data na pásu karet.
Je velmi výkonný a zároveň se velmi snadno používá a editor dotazů má pro uživatele Excelu velmi intuitivní uživatelské rozhraní. Mnoho kroků transformace lze snadno provádět z pásu karet editoru dotazů a k čištění a tvarování dat nepotřebujete znát žádný kód.
Za scénou uživatelsky přívětivého editoru Excel překládá každý krok procesu transformace z těchto příkazů na pásu karet do kódového jazyka Power Query M.
Tento příspěvek vás seznámí se základy kódového jazyka Power Query M a bude předpokládat, že již znáte základy Power Query.
Obsah
Co je kód M?
M je zkratka pro data Mash-up, protože power query je o připojování k různým zdrojům dat a jejich „Mashování“.
M kód je jazyk v zákulisí power query. Když v uživatelském rozhraní editoru power query vytvoříte transformaci dat, Excel pro tento dotaz napíše odpovídající kód M.
M je funkční jazyk, což znamená, že je primárně napsán pomocí funkcí, které jsou volány za účelem vyhodnocení a vrácení výsledků. Kód M má k dispozici velmi rozsáhlou knihovnu předdefinovaných funkcí a můžete si také vytvořit vlastní.
Kde můžete psát kód Power Query M?
Pokud chcete začít psát nebo upravovat kód M, budete potřebovat vědět, kde to můžete udělat. Existují dvě místa, kde je to možné, a to v panelu vzorců nebo v rozšířeném editoru.
Panel vzorců
Pro každý krok vytvořený v uživatelském rozhraní editoru můžete zobrazit odpovídající M kód v panelu vzorců.
Pokud panel vzorců nevidíte, přejděte na kartu Zobrazení a zkontrolujte, zda je zaškrtnuta volba Panel vzorců.
Můžete upravit M kód pro libovolný krok v dotazu kliknutím do vzorce a úpravou stávajícího kódu. Po dokončení můžete přijmout všechny změny buď kliknutím na zaškrtávací políčko, nebo stisknutím klávesy Enter. Změny můžete také zrušit kliknutím na symbol X nebo stisknutím klávesy Esc.
V dotazu můžete také vytvořit zcela nové kroky pomocí panelu vzorců kliknutím na symbol fx vedle panelu vzorců. Tím se vytvoří nový krok, který se podle názvu odkazuje na předchozí krok, a pak můžete vytvořit libovolný M kód, který potřebujete.
Pokročilý editor
Na liště vzorců se zobrazuje pouze M kód pro aktuálně vybraný krok v dotazu, ale v pokročilém editoru můžete zobrazit a upravit M kód pro celý dotaz.
Pokročilý editor můžete otevřít ze dvou míst na pásu editoru. Buď na kartě Domů, nebo na kartě Zobrazení stiskněte tlačítko Pokročilý editor.
Přes přívlastek „pokročilý“ je tento editor nejzákladnějším editorem kódu, který uvidíte, a neobsahuje (zatím) žádné funkce automatického doplňování intellisense, zvýrazňování syntaxe ani automatického formátování.
Pokročilý editor zobrazí název dotazu, zobrazí M kód dotazu a zobrazí upozornění na případné porušení syntaxe v M kódu. To je vše!
Standardní knihovna funkcí
Jelikož je kód M funkcionální jazyk, je vše o funkcích a kód M se dodává s rozsáhlou knihovnou předdefinovaných funkcí zvanou standardní knihovna.
Informace o všech dostupných funkcích standardní knihovny najdete na webové stránce společnosti Microsoft Power Query M Reference, včetně syntaxe funkcí a příkladů.
Standardní knihovnu lze také prozkoumat z editoru Power Query pomocí klíčového slova #shared.
Po zadání do panelu vzorců pak můžete prozkoumat všechny dostupné funkce kliknutím na slovo Function vpravo od názvu funkce. Najdete zde stejnou syntaxi a příklady jako na referenční webové stránce.
Citlivost na malá a velká písmena
Jednou z prvních věcí, kterou si musí někdo při psaní kódu v jazyce M uvědomit, je, že se jedná o jazyk, který rozlišuje malá a velká písmena.
To znamená, že x není totéž co X nebo „abc“ není totéž co „ABC“. To platí pro všechny hodnoty, proměnné, funkce atd.
Výrazy a hodnoty v Power Query
Power query je o výrazech a hodnotách.
Výraz je něco, co lze vyhodnotit a vrátit hodnotu v Power Query. 1 + 1 je výraz, který se vyhodnotí jako hodnota 2.
Hodnota je jeden údaj. Hodnoty mohou být jednotlivé hodnoty, například čísla, text, logické hodnoty, null, binární hodnoty, datum, čas, datetime, datetimezone nebo trvání.
Hodnoty mohou mít také složitější struktury než jednotlivé hodnoty, například seznamy, záznamy a tabulky. Můžete mít také hodnoty, které jsou kombinací seznamů, záznamů a tabulek. Seznamy seznamů, tabulky seznamů, tabulky tabulek atd… jsou všechny možné struktury hodnot.
Jednotlivé literální hodnoty
Jednotlivé literální hodnoty jsou základním stavebním kamenem všech ostatních hodnot.
- 123,45 je číselná hodnota.
- „Hello World!“ je textová hodnota.
- true je logická hodnota.
- null představují neexistenci hodnoty.
Jednoduché vnitřní hodnoty
Vnitřní hodnoty jsou konstruovány pomocí různých vnitřních funkcí.
- #čas(hodiny, minuty, sekundy)
- #datum(roky, měsíce, dny)
- #datetime(roky, měsíce, dny, hodiny, minuty, sekundy)
- #datetimezone( roky, měsíce, dny, hodiny, minuty, sekundy, offset-hours, offset-minutes)
- #duration(days, hours, minutes, seconds)
Příklad pro konstrukci data 2018-12-31 byste jej museli zkonstruovat pomocí vnitřní funkce #date(2018, 12, 31).
Strukturované hodnoty
Seznamy
Seznam je uspořádaná posloupnost hodnot.
Seznam můžete definovat pomocí kulatých závorek. {1, 2, 3} je seznam, který obsahuje čísla 1, 2 a 3. Protože je důležité pořadí, není to stejný seznam jako {3, 2, 1}.
{„Ahoj“, „Svět“} je seznam obsahující texty „Ahoj“ a „Svět“.
Možné jsou také seznamy seznamů, takže {{1, 2}, {3, 4, 5}} je seznam dvou seznamů. První seznam obsahuje čísla 1 a 2 a druhý seznam obsahuje čísla 3, 4 a 5.
Posloupné seznamy můžete vytvářet pomocí formátu {x..y}. {2..5} vytvoří seznam {2, 3, 4, 5}. Tento postup funguje také pro textové znaky. {„a“.. „d“} vytvoří seznam {„a“, „b“, „c“, „d“}.
Můžete mít také seznam bez položek, {} je prázdný seznam.
Protože jsou seznamy uspořádané, můžeme se na položky v seznamu odkazovat pomocí indexového čísla založeného na nule. {1, 2, 3}{2} se vyhodnotí jako 3, protože se jedná o druhou položku v seznamu (na základě nulového indexu).
Záznamy
Záznam je uspořádaná posloupnost polí. Každé pole se skládá z názvu pole, který pole jednoznačně identifikuje, a hodnoty pole, která může být libovolného typu.
Záznam můžete definovat pomocí hranatých závorek. Je to záznam se dvěma poli. První pole v záznamu má název pole FirstName a hodnotu „John“. Druhé pole v záznamu má název pole Věk a hodnotu 38.
Jsou možné i záznamy záznamů, ] je záznam s jedním polem s názvem pole Osoba a hodnotou pole, které je záznamem.
Jsou možné i prázdné záznamy, je prázdný záznam.
Na hodnotu pole v záznamu se můžete odkazovat pomocí jeho názvu. vyhodnotí se jako „John“.
Tabulky
Tabulka je uspořádaná posloupnost řádků, kde každý řádek je seznam.
Tabulky lze sestavit pouze pomocí vnitřní funkce. Tabulku můžete vytvořit pomocí funkce #table() ze seznamu nadpisů sloupců a seznamu řádků.
#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}})Vnitřní funkce #table() vytvoří tabulku se 2 sloupci, 3 řádky a nadpisy sloupců Písmena a Čísla.
Prázdnou tabulku je možné vytvořit pomocí prázdných seznamů ve vnitřní funkci #table(). Funkce #table({}, {}) vytvoří prázdnou tabulku.
Na libovolnou hodnotu v tabulce se můžete odkazovat pomocí indexu řádku založeného na nule a názvu záhlaví sloupce. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} Vyhodnotí se na „C“, protože se jedná o 2. řádek (na základě nulového indexu) sloupce Písmena.
Výrazy
Výrazy jsou cokoli, co lze vyhodnotit na hodnotu. To platí i pro samotné hodnoty. Například výraz 1 se vyhodnotí jako hodnota 1.
Ačkoli obvykle byste o výrazech uvažovali jako o složitějších operacích nebo funkcích.
Například:
- Výraz 1 + 1 se vyhodnotí jako 2.
- Výraz 3 > 2 se vyhodnotí jako true.
- Výraz „Hello “ & „World“ se vyhodnotí jako „Hello World“.
- Výraz Text.Upper(„Hello World“) se vyhodnotí jako „HELLO WORLD“.
Operátory
Kromě standardní knihovny má kód M také seznam speciálních funkcí, které se nazývají operátory. Ty přijímají přesně dvě hodnoty (nebo výrazy, které se vyhodnotí jako hodnota) a vracejí jednu hodnotu.
Aritmetické operátory
Kód M obsahuje základní aritmetické operátory, které byste očekávali a na které jste zvyklí z běžného Excelu +, -, * a /. Ty vám umožní sčítat, odčítat, násobit, respektive dělit hodnoty.
Ty lze použít i s různými jinými typy hodnot než jen s čísly. Například k datu můžete přičíst dobu trvání.
#date(2018,12,25) + #duration(7, 0, 0, 0, 0) se vyhodnotí jako 2019-01-01.
Porovnání
V kódu M můžete porovnávat hodnoty pomocí operátorů porovnání <, >, <=, >=, =, <>.
- x < y se vyhodnotí jako true, pokud je x menší než y.
- x > y se vyhodnotí jako true, pokud je x větší než y.
- x <= y se vyhodnotí jako pravdivé, jestliže x je menší nebo rovno y.
- x >= y se vyhodnotí jako pravdivé, jestliže x je větší nebo rovno y.
- x = y vyhodnotí se jako pravdivé, jestliže x je rovno y.
- x <> y vyhodnotí se jako pravdivé, jestliže x není rovno y.
Tyto příkazy lze použít s různými typy hodnot. Například můžete porovnat dva seznamy pomocí operátoru rovnosti.
{1,2,3,4} = {1,2,3} vyhodnotí se jako false, protože seznamy nejsou stejné.
Spojování a slučování
S operátorem ampersand & můžete spojovat text a slučovat seznamy, záznamy a tabulky.
Například:
"Hello " & "World" vyhodnotí se jako „Hello World“.
{1,2,3} & {3,4,5} se vyhodnotí jako {1,2,3,3,4,5}.
Logické
Můžete provádět operace nad logickými hodnotami (nebo výrazy, které se vyhodnocují jako logické hodnoty) pomocí operátorů not, and a nebo.
Komentování kódu
Jak se dá očekávat od každého programovacího jazyka, je možné přidávat do kódu komentáře.
V kódu jazyka M je možné používat dva typy komentářů. Jednořádkové komentáře a víceřádkové komentáře.
Jednořádkové komentáře
M code goes hereM code goes here //This is a single line commentM code goes hereJednořádkový komentář lze vytvořit tak, že před komentář vložíte dva znaky lomítka vpřed //. Cokoli na stejném řádku před tímto znakem bude interpretováno jako kód M, cokoli za ním bude interpretováno jako komentář.
Víceřádkové komentáře
M code goes here /*This is a commenton multiple lines*/ M code goes hereVíceřádkový komentář lze vytvořit umístěním komentáře mezi znaky /* a */. Cokoli mimo ně bude interpretováno jako kód M. Cokoli mezi nimi bude interpretováno jako komentář.
Příkaz let
Příkaz let umožňuje vyhodnotit sadu hodnot a přiřadit je jménům proměnných a poté je použít v následujícím výrazu, který následuje za příkazem in.
let a = 1, b = 2, c = a + bin cTento výraz se skládá ze tří výrazů, které jsou vyhodnoceny za příkazem let. Každý výraz je oddělen čárkou s výjimkou posledního výrazu před příkazem in. V tomto příkladu se celý výraz let a in vyhodnotí jako 3.
let c = a + b, b = 2, a = 1in cMožná si myslíte, že výrazy uvnitř příkazu let se musí objevit v pořadí, v jakém mají být vyhodnoceny. Není tomu tak! Výše uvedený kód je naprosto správný a také se vyhodnotí jako 3. Vyhodnocovací program kódu M automaticky vypočítá pořadí potřebných výpočtů na základě závislostí výrazů.
Pro člověka je samozřejmě snazší číst kód M, pokud je zapsán v pořadí vyhodnocování, ale je tu i další výhoda. Výrazy se v okně Aplikované kroky zobrazí jako samostatné kroky. Pokud jsou výrazy zapsány mimo pořadí, zobrazí se jako jeden kombinovaný krok.
let a = 1, b = 2in a + bVýrazy můžete vyhodnocovat také v rámci části in výrazu let… in….
Názvy proměnných
let #"Is this a variable name? Wow!!!" = 1 + 1in #"Is this a variable name? Wow!!!"Výrazům můžete přiřadit téměř libovolný název pomocí znaků #““. Můžete použít i znaky mezery a další speciální znaky. Jedinou výjimkou je použití vyhrazených klíčových slov.
Jména proměnných jsou to, co se bude zobrazovat v Aplikovaných krocích editoru dotazů, takže možnost používat znaky mezery je skvělá vlastnost.
Každý výraz
Výraz každý je zkratka pro deklarování funkcí přijímajících jediný parametr s názvem _ (podtržítko).
let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", each 2*)in #"Added Custom"V tomto příkladu vytváříme nový sloupec, který pro každý řádek vynásobí sloupec Čísla dvěma.
let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_)in #"Added Custom"Stejný dotaz můžeme vytvořit pomocí syntaxe podtržítka, která je sémanticky ekvivalentní příkazu each. Oba dotazy budou fungovat stejně.
Příkazy If Then Else
M kód je ve srovnání s jinými jazyky, pokud jde o logické výrazy, poměrně řídký. Nejsou k dispozici žádné příkazy select case nebo loop. K dispozici je pouze výraz if… then… else…
if then else Syntaxe je přímočará a podobá se většině ostatních programovacích jazyků. Může se objevit celá na jednom řádku, nebo může být pro snadnější čtení uvedena na samostatných řádcích.
Výrazy Try Otherwise
K chybám může dojít při pokusu o provedení operací, které vyžadují určité typy dat. Například při pokusu vynásobit číslo textovou hodnotou dojde k chybě.
let Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}), #"Added Custom" = Table.AddColumn(Source, "Product", each try * otherwise 0)in #"Added Custom"Chybám lze předejít použitím výrazu try… otherwise….
Tím se vyhnete chybám ve výsledcích dotazu a umožníte nahradit chyby libovolnou hodnotou nebo výrazem.
Funkce
Funkce je mapování z množiny hodnot parametrů na hodnotu. Spolu s funkcemi ve standardní knihovně vám kód M umožňuje vytvářet vlastní funkce.
let Product = (x,y) => x * y, Result = Product(2,3)in ResultTento dotaz definuje funkci, která násobí dvě čísla. Poté dotaz zavolá a vyhodnotí funkci s hodnotami 2 a 3, která se vyhodnotí jako 6.
Funkce s nepovinnými parametry
Existují dva typy parametrů funkce, povinný parametr a nepovinný parametr.
Povinné parametry musí být vždy zadány při volání funkce.
Povinné parametry nemusí být zadány při volání funkce. Pokud nepovinný parametr chybí, bude hodnota předaná funkci nulová.
let Product = (x, optional y) => if y is null then x else x * y, Result = Product(2)in ResultTato funkce má nepovinný druhý argument. Pak dotaz zavolá a vyhodnotí funkci pouze s použitím povinného parametru s hodnotou 2, která se vyhodnotí jako 2. Všimněte si, že funkce musí počítat s tím, že y je nulové, jinak by chybějící nepovinný argument mohl vést k chybnému vyhodnocení funkce.
Rekurzivní funkce
Je také možné zapsat funkci, která odkazuje sama na sebe, pomocí operátoru @ scoping.
let Fibonacci = (n) => if n = 1 then 1 else if n = 2 then 1 else @Fibonacci(n-1) + @Fibonacci(n-2), Result = Fibonacci(7)in ResultPříkladem rekurzivně definované funkce je Fibonacciho posloupnost. Další číslo v posloupnosti je definováno jako součet dvou předchozích čísel. Chcete-li tedy získat n-té číslo, musíte znát (n-1)-té a (n-2)-té číslo.
Tato funkce najde n-té Fibonacciho číslo tak, že sečte (n-1)-té a (n-2)-té Fibonacciho číslo.
Dotaz vyhodnotí jako 13, protože 13 je sedmé Fibonacciho číslo.
Zde jsou uvedeny další užitečné příklady, kdy můžete pomocí rekurzivních funkcí napodobit funkci TRIM aplikace Excel pro odstranění přebytečných mezer mezi slovy nebo hromadné vyhledávání a nahrazování hodnot na základě seznamu.
Dotazovací funkce
Výše uvedené příklady definovaly funkci v rámci dotazu a poté ji volaly a vyhodnocovaly v rámci dotazu.
Je také možné vytvořit dotaz, který je funkcí (dotazovací funkcí) a může být volán a vyhodnocován z jiných dotazů.
let FunctionResult = (Argument1, Argument2,...) => let /*M code to evaluate in the function goes here*/ in Resultin FunctionResultToto je obecný formát potřebný k vytvoření dotazovací funkce. Všimněte si, že budete potřebovat příkaz let… v… uvnitř příkazu let… v… dotazovací funkce, abyste mohli provést více kroků.
Závěry
Power query je skvělá funkce v Excelu, která vám může pomoci automatizovat a zjednodušit import a transformaci dat.
Můžete toho hodně udělat jen pomocí grafického rozhraní point and click, aniž byste se dotkli kódu M. Jakmile se však vaše požadavky stanou pokročilejšími, může nastat chvíle, kdy budete muset vytvořený kód M upravit nebo napsat vlastní od začátku.
Každý nový programovací jazyk je nečitelný, pokud nejprve neznáte jeho základy. Doufejme, že tento průvodce vám umožní začít pracovat s kódem M, abyste mohli začít vytvářet pokročilejší dotazy.