M-Language , M-kód - základní teorie v Power Query Excel

Úvodem aneb Co to je M-code

Je o součást PowerQuery editoru. Někdy označován jako M-code, M-language, M-kód, M-jazyk.

Jde o funkční jazyk pro vytváření dotazu na práci s tabulkami. V základu si jej píše Excel v PowerQuery sám na základě vašich čiností, ale proč se na tento jazyk nepodívat hlouběji? Zjistíte, že jazyk vůbec není složitý a dají se v něm dělat jednoduše, věci, které trvají dlouho. Efektivní využití schopností M-kódu vám ušetří spoustu práce.

V dalším textu se podíváme na:

• Zobrazení M-kódu - řádku vzorců a rozšířeného editoru
• Základní úpravy práce s M-kodem
• Hodnoty (Values) a Dotazy (Expressions)
• Samostatné hodnoty (jednotlivé)
• Samostatné hodnoty vytvořené funkcí
• Strukturovaná data
• Záznam Rekord
• Tabulky
• Dotazy (výrazy) Expressions podrobněji
• Aritmetické operátory
• Porovnání - porovnávací operátory
• Zřetězení
• Logický
• Komentáře
• Len - in konstrukce
• Each konstrukce
• Variabilní jména - Variable Names
• if then else
• Funkce
• Chyby
• try ... otherwise
• Jiné ukázky kódu

Zobrazení M-kódu

Jak jej mohu vidět/zobrazit? Aneb jak zobrazit řádek vzorců?

Pokud jste v PowerQuery editoru, musíte zobrazit řádek vzorců. Na pásu karet Zobrazení stačí zaškrtnout Řádek vzorců a objeví se řádek vzorců s M-kódem. Doporučuji také zapnout i Nastavení dotazů (pokud nemáte/nevidíte).

Případně si můžete zobrazí rozšířený editor. Pokud jste v PowerQuery editoru, musíte zobrazit řádek vzorců. Na pásu karet Zobrazení kliknete na ikonu Rozšířený editor

Rozšířený editor obsahuje tři základní části:

• Jméno tabulky
• Vlastní kód
• Případné chyby

Základní úpravy práce s M-kódem

V základu je M kód o hodnotách (Values) a dotazech/výrazech (Expressions) o čemž se rozepíšu dále v tomto článku.

Pokud již máte M kód vytvořený (například klikáním na příslušné ikony) v libovolné vybrané sekci (kroku) z dotazu, tento kód upravíte kliknutím do vzorce (přidáte a vylepšíte). Následně potvrdíte změnu přes klávesu Enter. Nebo změny můžete zahodit klikem na X nebo stisknutím Esc.

Dále do řádku vzorců můžete přes Fx vkládat funkce (o tom ale v samostatném článku).

Pozor při praní jazyk respektuje malá a velká písmena "JakNaExcel" je něco jiného než "Jaknaexcel", jiná velikost písmen > jiné slovo.

Více o referencích je k dispozici přímo u Microsoftu na https://docs.microsoft.com/cs-cz/powerquery-m/index

Případně knihovny dostupné zadáním = #share do příkazového řádku (zatím si ze jen odložím):

= #shared

Hodnoty (Values) a Dotazy (Expressions)

M-kódu se rozděluji na dvě (tři) základní sekce. Nebo-li první sekce jsou Hodnoty, konstanty (Values), druhá sekce jsou Dotazy / výrazy (Expressions). Tou třetí sekci někdo označuje poznámky.

Hodnota je kus datové informace nebo-li:

• Hodnoty mohou být:
  • čísla
  • text
  • logická hodnota
  • binární
  • datu
  • čas
  • ...
• Hodnoty jsou také:
  • seznamy
  • záznam v tabulce (řádek(y))
  • seznamy seznamů atd.
  • seznamy tabulek
  • tabulka
  • ...

Dotaz je něco, co se dá vyhodnotit. Nebo-li pokud vyhodnotím mohu vrátit zpět nějakou "hodnotu". Z dotazu 1 + 1 mohu tento výraz vyhodnotí a získat hodnotu 2.

Dotazy budou uvedeny dále v tomto článku.

Poznámky pokud chcete mít v kódech pořádek a vyznat se v nich můžete si je doplnit i o poznámky. Nejsou nezbytné pro funkci kódu, ale pro váš lepší přehled a orientaci jsou mnohdy nezbytné (osobně poznámky potřebuji). // komentář nebo */ pro víceřádkový /*, opět bude dále zmíněno.

Pojďme se na hodnoty a dotazy podívat podrobněji.

Samostatné hodnoty (jednotlivé)

Samostatné hodnoty jsou prvkem pro všech ostatních hodnoty (např. záznam se skládá ze samostatných hodnot viz dále) .

• 125.44 číslo
• "JakNaExcel" text
• True logická hodnota
• null skutečně prázdná hodnota, i designově graficky se liší v tabulkách

Samsotatné hodnoty vytvořené funkcí

Pomocí funkcí můžete sestavit hlavně časové a datumové údaje (skládají se pomocí funkcí):

• #time (hodiny, minuty, sekundy) - vytvoření času - #time (10, 42, 20)
• #date (roky, měsíce, dny) - vytvoření datum - #date (2018, 12, 31)
• #datetime (roky, měsíce, dny, hodiny, minuty, sekundy) - - ...
• #datetimezone (roky, měsíce, dny, hodiny, minuty, sekundy, offset-hodiny, offset-minuty) - - ...
• #duration (dny, hodiny, minuty, sekundy) - - ...

Více o funkcích v samostatné kapitole.

Strukturovaná data

Jedná se hlavně o seznamy

Klasický seznam

Jednoduchý seznamy, třeba názvy dnů, měsíců:

= {1, 2}

Vícerozměrné seznam

 {{1, 2}, {3, 4, 5}}

PowerQuery u vícerozměrných seznamů zobrazí v první tabulce hodnota List a pak se prokliknutím teprve zobrazí hodnoty daném seznamu.

Sekvenční seznamy

Sekvenční seznamy můžete vytvářet ve formátu {x..y}, kdy x je od jaké hodnoty dvě tečky a pak do jaké hodnoty. Například:

= {"a".."f"}

Pozor seznamy neplést se záznamem (Records).

Záznam Rekord

Záznam je uspořádaná sekvence polí. Každé pole se skládá z názvu pole, které jedinečně identifikuje toto pole a jeho hodnoty. Každá hodnota může být libovolným typem hodnoty (O hodnotách viz výše).

Záznam (Rekord) se definuje pomocí hranatých závorek [], pak se vloží jméno které toto pole identifikuje. Znaménko rovná se. Posléze hodnota tohoto pole.

Ukázka:

= [Jméno = "Pavel", Funkce = "MVP", Město="Brno"]

Záznamy mohu dávat do záznamu. V PowerQuery pak uvidíte Record, který můžete prokliknout (podobně jako u seznamu, který je popsán výše).

= [Osoba = [Jméno = "Jan", Věk = 38]]

Jsou možné i prázdné záznamy, [] je prázdný záznam.

V Záznamu (Record) se dá i odkazovat na onen záznam, ale o tom v samostatném článku.

Tabulky

Tabulka je vlastně uspořádaná posloupnost řádků, kde každý řádek je seznam (viz popis seznamu výše).

Tabulka se sestaví pomocí funkce #table kdy jednotlivé řádky se zapisují jako seznam {"A", 1} s skupina záznamu je opět ve složených závorkách.

= #table({"Pismena", "Čísla"}, {{"A", 1}, {"B", 2}, {"C", 3}})

Je možné vytvořit prázdnou tabulku pomocí prázdných seznamů ve vnitřní funkci #table (). #table ({}, {}) vytvoří prázdnou tabulku. Stejně tak se dá na tabulku odkazovat s indexem řádků, kdy řádek začíná od nuly a názvem sloupce v hranaté závorce, ale o tom v samostatném článku.

Tabulky lze vytvářet například ze seznamu (Listu):

// ze seznamu = Table.FromList({"a", "b", "c", "d"}, null, {"Písmena"}) // Ze záznamu (Record) = Table.FromRecords({[ID = 1, Jmeno = "Pavel", Tit = "MVP"], [ID = 2, Jmeno = "Eva", Tit = "Ing"], [ID = 3, Jmeno = "Iva", Tit = null]})

Podrobněji se na tabulky podívám v sanostatném článku

Dotazy (výrazy) Expressions podrobněji

Dotaz (Výrazy, vzorec) je cokoli, co lze vyhodnotit a vrátit hodnotu. Ono i hodnota je vlastně dotaz ;) Hodnota 1 vyhodnotí a vrátí 1. Ukázky výrazů:

• 1 + 1 - se vyhodnocuje jako 2
• 3 > 2 - se vyhodnotí jako pravdivý
• "Pavel" & "Lasák - se vyhodnotí jako "Pavel Lasák"
• Text.Upper ("Hello World") - se vyhodnotí jako "HELLO WORLD"

V dotazech se podíváme na praktické použití.

Aritmetické operátory

M kód umí (stejně jako jiné programovací jazyky) klasické aritmetické operátory +, -, * a / (což je logiocké ale neškodí zmínit). Umožní přidávat, odečítat, násobit a dělit hodnoty. Tyto operace se dají použít například v práci s konstantami.

#date (2019,11,15) + #duration (1, 2, 3, 4)

Porovnání - porovnávací operátory

Hodnoty v M kódu můžete porovnat pomocí operátorů porovnání =, <, >, <=, >=, =, <>.

• x = y se vyhodnotí jako true, pokud x se rovná y
• x > y se vyhodnotí jako true, pokud x je větší než y
• x < y se vyhodnotí jako true, pokud x je menší než y
• x <= y se vyhodnotí jako true, pokud x je menší nebo rovno y
• x >= y se vyhodnotí jako true, pokud x je větší nebo rovno y
• x <> y se vyhodnotí jako true, pokud x není rovno y

Ono lze porovnávat třeba seznamy atd.

{1,2,3,4} = {1,2,3} nebo {1,2,3} = {1,2,3}

Zřetězení (sloučení)

Můžeme zřetězit textové konstanty, sločovat seznamy, záznamy z tabulky atd.

• "Ahoj" & "světe" se vyhodnotí jako "Ahoj světe"
• {1,2,3} & {3,4,5} se vyhodnotí, spojí na seznam {1,2,3,3,4,5}

"Ahoj" & "světe" {1,2,3} & {3,4,5}

Logický

V M-kódu můžete provádět operace s logickými (booleovskými) hodnotami (nebo výrazy, které se vyhodnocují jako True False / Pravda Nepravda). Máte k dispozici operátory:

• not - negace / opak
• and - a
• or - nebo

[Jmeno] = "Pavel" and [Titul] = "MVP" další ukázky přípravě

Využití například u konstrukce if then else.

Komentáře

Potřebujete-li M-kód komentovat. Lze využít jeden řádek, nebo více řádků:

• // komentář řádek
• /* Více řádků*/

// komentář řádek /* víceřádkový komentář */

Len in - konstrukce

Konstrukce (příkaz) let umožňuje, aby byla vyhodnocena sada hodnot a následně přiřazena proměnným, která se přiřadí následujícímu výrazu, který následuje za příkazem in.

let b = 2, a = 1, c = a + b in c

Na konstrukci Len se podívám podrobněji v samostatném článku, jen zde zmiňuji pro dokreslení různých možností.

Variabilní jména - Variable Names

K výrazům (proměnným) můžete přiřadit téměř libovolné jméno pomocí znaků #

let #"Jak na Excel je super!" = 1 + 1 ¨ in #"Jak na Excel je super!"

Jak na jméno a co lze přiřazovat zmíním v samostatném článku.

Each Statements

Přes each mohu například doplnit všechny proměné v parametru (přičíst číslo k hodnotám ve sloupci).

let Zdroj = #table({"Čísla"}, {{1}, {2}, {3}, {42}}), #"PřidanoUživatelem" = Table.AddColumn(Zdroj, "Přičtena jednočka", each [Čísla] + 1) in #"PřidanoUživatelem"

If Then Else - konstrukce

Klasické podmínkové funkce pokud je splněno provede se něco jinak něco jiného.

Teorie If then Else je:

if [logický výraz k testování] then [když je výraz pravda tak se provede] else [jinak se provede toto]

V podmínkách se dají využít i logické operátory and or. U složitějších podmínek nezapomenout na závorky!

[Jmeno] = "Pavel" and [Titul] = "MVP" .. [Jmeno] = "Pavel" or [Titul] = "MVP"

Například pokud sloupec se jménem Id je rovno jedničce zapíše se 2 jinak 0 (nula).

if [Id] = 1 then 2 else 0

V tomto zápisu to vapadá možná nepoužitelně, ale použijete-li ve spojení s konstrukcí each již vám dává smysl, například:

let Zdroj = Excel.CurrentWorkbook(){[Name="Tabulka1"]}[Content], #"Změněný typ" = Table.TransformColumnTypes(Zdroj,{{"Id", Int64.Type}}), #"Přidané: Vlastní" = Table.AddColumn(#"Změněný typ", "Vlastní", each if [Id] = 1 then 2 else 0) in #"Přidané: Vlastní"

Syntaxe se může zobrazit na jednom řádku, nebo může být pro snadnější čtení rozdělena na samostatné řádky. Ale o tom v samostatném článku, kde se podmínkám podívám na zoubek podrobněji.

Funkce

Funkce na základě vstupních parametru(ů) provede příslušný propočet a vrátí příslušný výsledek:

let Produkt = (x, y) => x * y + 100, Výsledek = Produkt (2,3) in Výsledek

Chyby

Pokd například deklaraci funkce, podmínky zadáte chybně, může se objevit chyba (v rozšířeném editoru je hned vidět, v řádku vzorců až po potvrzení Enter) například v ukázce chybí uvozovky za příjmením:

= "Pavel" & "Lasák

try ... otherwise

Využití například na ošetřování chyb

= try "JakNaExcel" + 1 otherwise "Toto je chyba."

Případně přes podmínky

let result = try "Excel"+1, vysledek = if result[HasError] then "Asi chyba, Máchale." else result[Value] in vysledek

Podrobněji v samostatném článku (doplněno na základě tipu v diskuzi na LikendIn).

Jiné ukázky kódu

V samostatných článcích následně ukáži jak na další ukázky. Využití u dynamického odkazování v M kódu.

Načtení dat = Excel.CurrentWorkbook(){[Name="Tabulka1"]}[Content] Filtrace = Table.SelectRows(Zdroj, each ([Id] <> 2)) Změna typu = Table.TransformColumnTypes(#"Filtrované řádky",{{"Id", Int64.Type}}) Přejmenování = Table.RenameColumns(#"Změněný typ",{{"Id", "Pokus"}}) Přidání sloupce = Table.AddColumn(#"Přejmenované sloupce", "Součet", each [Pokus] + 1, type number) Odebrání sloupce = Table.RemoveColumns(#"Vložené: Součet",{"Součet"})

Závěrem

Narazili jste na nějaký problémy, máte tip jak článek vylepšit co doplnit? Můžete se zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:02

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od dubna roku 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím 8x za sebou). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 20 let (o Excel píší přes 25). Zdarma je zde přes 1.500 návodu, tipů a triků, včetně přes 350 různých šablon, sešitů a přes 70 taháků v pdf.