Jak na Excel Obsah cvičení M-language Teorie Strukturované data Pavel Lasák "Lektor, expert na Microsoft Excel, držitel prestižního ocenění Microsoftu MVP v České republice" 5002722 Další informace ke cvičení: https://office.lasakovi.com/excel/Query-editor/M-code-M-Language-zaklady-PowerQuery-Excel/ "Copyright, Pavel Lasák 2019 rev 2020" ##### Sheet/List 2 ##### "M-Language , M-kód" https://office.lasakovi.com/ Co to je "Je o součast 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?" Jak jej mohu vidět? “try otherwise V PowerQuery editoru musíte spustit a zobrazit řádek vzorců Případně si můžete zobrazi rozšířený editor. Co který příkaz znamená se pokusim rozebrat dále "= try ""foo""+1 otherwise ""There was an error""" V základu je M kód o hodnotách (Values) a dotazech/výrazech (Expressions) o čemž se rozepíšu dále "result = try ""foo""+1, if result[HasError] then ""There was an error"" else result[Value]" Popis okna Jméno tabulky "try error ""negative unit count"" otherwise 42 " Vlastní kód Případné chyby Základní úpravy "M kod v libovolném krok (sekci) z dotazu, upravite kliknutím do vzorce a přidáním, vylepšením. Potvrzení změnpřes klávese Enter. Změny můžete zahodit klikem na X nebo stisknutím Esc." Případně přes Fx můžete vkládat funkce (o tom v samostatném článku) "Pozor při praní jazyk respektuje malá a velká písmena ""JakNaExcel"" je něco jiného než ""Jaknaexcel""" více o referenci u microsoftu https://docs.microsoft.com/cs-cz/powerquery-m/index Případně k dostupným knihovnám se dostanete přes slovo #shared hodnotách (Values) a dotazech (Expressions) "Výraz 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 vyhodnoti a získat hodnotu 2." Hodnota je kus datové informace nebo-li Hodnoty mohou být čísla text logická hodnota binární datu čas … Hodnoty jsou ale i: seznamy záznam v tabulce (řádek) seznamy seznamů atd. seznamy tabulek tabulka Poznámky a komentáře // komentář Jednořádkový */ this is my více řádkový comment */ Pojďme se na hodnoty podívat podrobněji Samostatné hodnoty (jednotlivé) Samostatné hodnoty jsou základním stavebním kamenem pro všech ostatních hodnoty (např. záznam se skládá ze smamostatný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" Samostatné hodnoty vytvořené funkcí Půjde hlavně o časové funkce "#time (hodiny, minuty, sekundy)" "#date (roky, měsíce, dny)" "#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)" "Například k vytvoření data 2018-12-31 je nutné jej zkonstruovat pomocí vnitřní funkce #date (2018, 12, 31)." Strukturovaná data seznamy "= {1, 2}" " {3, 4, 5}" " {{1, 2}, {3, 4, 5}}" sekvenční seznamy "= {""a""..""d""}" "Sekvenční seznamy můžete vytvářet ve formátu {x..y}. {2..5} vytvoří seznam {2, 3, 4, 5}. Toto funguje také pro textové znaky. {""A"" .. ""d""} vytvoří seznam {""a"", ""b"", ""c"", ""d""}." Seznam seznamů "{{1, 2}, {3, 4, 5}}" "Jsou také možné 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." PowerQuery zobrazí List a prokliknutím se teprve zobrazí hodnoty daném seznamu… Neplést se záznamem (Records) Záznam Rekord "[FirstName = ""John"", Age = 38]" "Záznam je uspořádaná sekvence polí. Každé pole se skládá z názvu pole, které jedinečně identifikuje pole a hodnoty pole, která může být libovolným typem hodnoty." "Záznam je uspořádaná sekvence polí. Každé pole se skládá z názvu pole, které jedinečně identifikuje pole a hodnoty pole, která může být libovolným typem hodnoty." "Záznam můžete definovat pomocí hranatých závorek. [FirstName = ""John"", Age = 38] je záznam se dvěma poli. První pole v záznamu má název pole Jméno a hodnotu „Jan“. Druhé pole v záznamu má název pole Věk a hodnotu 38." "Záznamy záznamů jsou také možné, [Osoba = [Jméno = ""Jan"", Věk = 38]] je záznam s jedním polem s názvem pole Osoba a hodnotou pole, která je záznamem." "Jsou také možné prázdné záznamy, [] je prázdný záznam." "Můžete odkazovat na hodnotu pole v záznamu podle názvu pole. [Jméno = “Jan”, Věk = 38] [Jméno] se vyhodnotí jako “Jan”." Tabulky "#table({""Letters"", ""Numbers""}, {{""A"", 1}, {""B"", 2}, {""C"", 3}})" "Tabulka je uspořádaná posloupnost řádků, kde každý řádek je seznam." Tabulky lze sestavit pouze pomocí vlastní funkce. Tabulku můžete sestavit pomocí funkce #table () ze seznamu záhlaví sloupců a seznamu řádků. "#table ({""Dopisy"", ""Čísla""}, {{""A"", 1}, {""B"", 2}, {""C"", 3}}) vytvoří tabulku se 2 sloupci, 3 řádky a záhlaví sloupců Písmena a čísla." "Je možné vytvořit prázdnou tabulku pomocí prázdných seznamů ve vnitřní funkci #table (). #table ({}, {}) vytvoří prázdnou tabulku." "Na libovolnou hodnotu v tabulce můžete odkazovat s indexem řádků na základě nuly a názvem záhlaví sloupce. #table ({""Dopisy"", ""Čísla""}, {{""A"", 1}, {""B"", 2}, {""C"", 3}}) {2} [Dopisy] se vyhodnotí jako „C “, Protože se jedná o 2. řádek (na základě nulového indexu) sloupce Dopisy." Dotazy (výrazy) Expressions podrobněji "Dotaz (Výrazy, vzorec) je cokoli, co lze vyhodnotit a vrátit hodnotu. Ono i hodnota je vlasně dotaz ;) Hodnota 1 vyhodnotí a vrátí 1." Výraz 1 + 1 se vyhodnocuje jako 2. Výraz 3> 2 se vyhodnotí jako pravdivý. Výraz „Hello“ a „World“ se hodnotí jako „Hello World“. Výraz Text.Upper („Hello World“) se vyhodnotí jako „HELLO WORLD“. pojďme se podívat do v dotazech výrazech funkcích lze používat Aritmetické operátory "M kód umí klasické aritmetickými operátory +, -, * a / (což je logiocké ale neškodí zmínit). Umožní přidávat, odečítat, násobit a dělit hodnoty." "#date (2018,12,25) + #duration (7, 0, 0, 0) se vyhodnotí na 2019-01-01." 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 je větší 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 se rovná y." "x <> y bude vyhodnoceno jako true, pokud x není rovno y." "{1,2,3,4} = {1,2,3}" Zřetězení (sloučení) "Můžete zřetězit textové a sloučené seznamy, záznamy a tabulky pomocí ampersand & operator." „Ahoj“ & „Svět“ se vyhodnotí jako „Ahoj svět“. "{1,2,3} & {3,4,5} se vyhodnotí na {1,2,3,3,4,5}." Logický "Můžete provádět operace s booleovskými hodnotami (nebo výrazy, které se vyhodnocují v booleovských hodnotách) s operátory not, a nebo." Komentáře //This is a single line comment M code goes here /*This is a comment on multiple lines*/ M code goes here Len Nechte prohlášení "Příkaz let umožňuje, aby byla sada hodnot vyhodnocena a přiřazena názvům proměnných a poté použita v následujícím výrazu, který následuje za příkazem in." podívám se podrobněji v samosttném článku jen to mám zde odložené a přidáám odkaz na další článek Variable Names let " #""Is this a variable name? Wow!!!"" = 1 + 1" in " #""Is this a variable name? Wow!!!""" Each Statements Každá prohlášení "Každý výraz je zkratkou pro deklarování funkcí, které berou jeden parametr s názvem _ (podtržítko)." let " Source = #table({""Numbers""}, {{1}, {2}, {3}, {4}, {5}})," " #""Added Custom"" = Table.AddColumn(Source, ""Double"", each 2*[Numbers])" in " #""Added Custom""" If Then Else Statement "Pokud jde o logické výrazy, kód M je ve srovnání s jinými jazyky dost řídký. Nejsou k dispozici žádné příkazy case nebo loop. K dispozici je pouze výraz if… then… else…." if [logický výraz k testování] "pak [udělej, když je to pravda]" else [to když falešný] Syntaxe je přímá a je jako většina ostatních programovacích jazyků. Může se zobrazit vše na jednom řádku nebo může být pro snadnější čtení zobrazen na samostatných řádcích. Funkce Funkce je mapování ze sady hodnot parametrů na hodnotu. Spolu s funkcemi ve standardní knihovně vám M kód umožňuje vytvářet vlastní funkce. nechat "     Produkt = (x, y) => x * y," "     Výsledek = Produkt (2,3)" v      Výsledek Opět v samostném článki Jiné ukázky 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""})" Další ? Napadá vás co do tohoto přehledového článku doplnit prosím o informaci ##### Sheet/List 3 ##### Dotaz1 1 2 ##### Sheet/List 4 ##### "M-Language , M-kód" https://office.lasakovi.com/ Strukturované data Klasický seznam "= {1, 2}" Vícerozměrné seznam "= {{1, 2}, {3, 4, 5}}" Sekvenční seznamy "= {""a""..""f""}" Record "= [Jméno = ""Pavel"", Funkce = ""MVP"", Město=""Brno""]" "= [Osoba = [Jméno = ""Jan"", Věk = 38]]" Tabulky "= #table({""Pismena"", ""Čísla""}, {{""A"", 1}, {""B"", 2}, {""C"", 3}})" "// 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]})" ##### Sheet/List 5 ##### "M-Language , M-kód" https://office.lasakovi.com/ Tabulky "= #table({""Pismena"", ""Čísla""}, {{""A"", 1}, {""B"", 2}, {""C"", 3}})" "// 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]})"