Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » Query-editor » M-code-M-Language-zaklady-PowerQuery-Excel
Microsoft Excel logo

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

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Co to je, co umí, jak zobrazit M-kód v Power Query v Excel

V Power BI funguje velice podobně.

Ú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

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).

Power Query - Excel - zobrazení řádek vzorců

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

Power Query - Excel - 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 Power Query - Excel - rozšířený editor

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) - - ...
Power Query - Excel - rozšířený editor

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} Power Query - Excel - strukturovaná data

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"} Power Query - Excel - sekvenční seznamy

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).

Power Query - Excel - Záznam - Record
= [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}}) Power Query - Excel - Záznam - Record

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ář */ Power Query - Excel - komentare

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 Power Query - Excel - Len in

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!" Power Query - Excel -  Variable Names

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" Power Query - Excel -  Variable Names

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í" Power Query - Excel -  if - then - else

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 Power Query - Excel -  if - then - else

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 Power Query - Excel -  Jazky M, chyby deklarace

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 Power Query - Excel -  try...otherwise chyby deklarace

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: 18.11.2019 15:24

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). 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 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 300

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.