|
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ě.
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:
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:
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
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:
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 jsou prvkem pro všech ostatních hodnoty (např. záznam se skládá ze samostatných hodnot viz dále) .
Pomocí funkcí můžete sestavit hlavně časové a datumové údaje (skládají se pomocí funkcí):
Více o funkcích v samostatné kapitole.
Jedná se hlavně o seznamy
Jednoduchý seznamy, třeba názvy dnů, měsíců:
= {1, 2}
{{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 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 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.
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
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ů:
V dotazech se podíváme na praktické použití.
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)
Hodnoty v M kódu můžete porovnat pomocí operátorů porovnání =, <, >, <=, >=, =, <>.
Ono lze porovnávat třeba seznamy atd.
{1,2,3,4} = {1,2,3}
nebo
{1,2,3} = {1,2,3}
Můžeme zřetězit textové konstanty, sločovat seznamy, záznamy z tabulky atd.
"Ahoj" & "světe"
{1,2,3} & {3,4,5}
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:
[Jmeno] = "Pavel" and [Titul] = "MVP"
další ukázky přípravě
Využití například u konstrukce if then else.
Potřebujete-li M-kód komentovat. Lze využít jeden řádek, nebo více řádků:
// komentář řádek
/* víceřádkový
komentář */
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í.
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.
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"
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 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
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
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).
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"})
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
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.
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ů.
Můžete být prvními co zanechají smysluplný komentář.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
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 - 2021 |