Objekty jazyka M (language) v Editoru Power Query

Úvodem do jazyka M

Uživatelské rozhraní Editoru Power Query je úžasné, mnoho operací provedete klikem na ikony pasu karet. Přesto se někdy stane, že budete potřebovat udělat věci, které nejsou v uživatelském rozhraní zabudovány (odkaz na předchozí krok). Pokud požadovanou činnost v menu nenajdete, stačí v Editoru Power Query a využít jazyk M (M-language).

V tomto článku se postupně podíáate na různé objekty, které můžete v jazyku M mít (seznamy, záznamy, tabulky) a jak je vložit. Pro přehlednost a možnost článek doplňovat a rozšiřovat je rozdělen na jednotlivé kapitoly:

• Prázdný dotaz
• Přidat prázdný dotaz v Editoru Power Query
• Seznam (List)
  • Seznam (List)
  • Seznam (List) jako řada
  • Speciální (List) seznamy
  • Seznam převést na tabulku
  • Seznam seznamu na tabulku
• Záznamy (Records)
  • Záznamy (Records) teorie
  • První záznam (Records)
  • Seznam (List) Záznamů (Records)
  • Speciální seznam záznamů
  • Záznamy (Records) do tabulky
• Tabulky (Table)
  • Tabulka základ
• Související články
• Ke stažení

Prázdný dotaz

Pro další ukázky nejprve spusťte Excel a vložte prázdný dotaz. Na pásu karet Data ikona Načíst data z menu vyberte Z jiných zdrojů a vybrat Prázdný dotaz.

Zobrazí se vám Editor Power Query.

Podobně funguje i v Power BI při přidání prázdného dotazu.

Přidat prázdný dotaz v Editoru Power Query

Pro testy se hodí vložit prázdný dotaz z menu v Power Query Editoru.

Menu Domů > Nový zdroj > Jiné zdroje > Prázdný dotaz.

Seznam (List)

V programu Editor Power Query lze seznam (v angličtině List) identifikovat podle přítomnosti složených závorek {}, přičemž položky v seznamu jsou oddělené čárkami.

Pokud jsou v seznamu čísla, stačí ej oddělovat čárkou, pokud půjde o textové položky musí se zapisovat mezi uvozovky (podobně jako ve vzorci Excelu).

Jako první zkuste zadat do řádků vzorců:

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

Dotaz můžete přejmenovat na "Cisla". Stačí v Názvu změnit název na Cisla.

Podle předchozí kapitoly "Přidat prázdný dotaz v Editoru Power Query" přidejte prázdný dotaz, který můžete pojmenovat Písmena a vložte vzorec.

={"A","B","C","D","E","F","G","H","I","J"}

Přidejte prázdný dotaz (pojmenovat můžete SeznamSeznamu) a můžete přidat seznam seznamů vložením vzorce:

= {2,"Pavel Lasák",{42,1}}

Seznam jako řada

Vytvořit seznam oddělených čárkou u malých číselných řad není problém. Co když, ale chcete vytvořit řadu 1 až 365? Vypsat všech 365 čísel se vám asi nebude chtít tvořit. M můžete ale využít řadu od do. Zadejte vzorec do nového prázdného dotazu (můžete jej mít pojmenován například Řada):

={1..365}

Lze i u písmen, opět přidejte nový dotaz pojmenujte PísmenaŘada a zadejte vzorec:

={"A".."Z"}

Bohužel zatím funguje jen u jednopísmenných rozsahů. U více písmenných ne.

={"A".."ZZ"}

Speciální seznamy

Seznam mající ve textech středníky a čárky, opět do prázdného dotazu pojmenovaného Středníky

= {"Pavel, Lasák", "JakNaExcel.cz", "Pavel; Lasák"}

Co ale samostatné uvozovky, opět do nového dotazu (Uvozovky) můžete vložit vzorec:

= {""""""," "" "," ' "," """,""""}

Seznam převést na tabulku

Budete-li potřebovat převést seznam na tabulku stačí vybrat seznam (v ukázce si vyberu seznam Cisla), který chcete převést a klik na ikonu Do tabulky z pásu karet Transformace.

V zobrazeném dialogovém okně můžete zvolit oddělovač, a co se má stát s nadbytečnými sloupci.

Klikem na OK. A máte hotovo.

Seznam seznamu na tabulku

Přidejte nový prázdný dotaz (SeznamyVSeznamu), která bude obsahovat seznam seznamu. Stačí složit vzorec:

= {{1,"Pavel"},{2,"Lasák"},{3,"Excel"},{4,"PowerBI"}}

Protože se jedná o seznam složený ze seznamu, potřebujete se dostat k datům. Což jednoduše provedete, že nejprve převedete na tabulku. Klikem Do tabulky z pásu karet Transformace. V zobrazeném okně stačí klik na OK.

Klik na "dvojšipku" u názvu sloupce a pokud vyberete Rozbalit do nových řádku. Obdržíte hodnoty seřazené pod sebou.

Zde budete mít malou komplikací, protože hodnoty jsou pod sebou. Pokud je budete chtít dostat do sloupců, bude potřeba přidat indexovaný sloupec, následně zbytek po dělení požadovaným počtem sloupců, "pivotovatů. Což je jen trochu pracné.

Druhá možnost je vybrat druhou možnost, která se po kliku na "dvojšipku" objeví Extrahovat hodnoty....

V dialogovém okně Extrahovat hodnoty ze seznamu zvolíte oddělovat, kterým se jednotlivé položky ze seznamu rozdělí. Například čárka.

Klikem na OK obdržíte tabulku, kde jednotlivé položky ze seznamu budou odděleny čárkou, s čímž si poradíte rychleji. Rozdělit sloupce podle čárky.

Záznamy (Records) teorie

Zatímco seznam (List) lze popsat jako jeden vertikální sloupec dat. Záznam (Records) lze naopak popsat jako horizontální, vícesloupcový seznam. Záznam (Records) si lze představit jako jeden řádek databáze, který obsahuje všechny relevantní informace. Například informace týkající se konkrétního zákazníka, zaměstnance, transakce, automobilu, výrobků.

V aplikaci Editor Power Query se záznamy mohou zobrazovat ve sloupcích tabulky nebo v seznamech při načítání dat.

Záznamy jsou:

• 1 Každý úplný záznam je obklopen hranatými závorkami []
• 2 Každé pole záznamu (sloupec) musí mít definovaný název (jméno sloupce), za kterým následuje znak =
• 3 Následují údaje pro dané pole, které jsou v případě textových údajů obklopeny uvozovkami
• 4 Každá dvojice název pole a data které obsahuje je oddělena čárkou ,

První záznam (Records)

Přidejte si do Editoru Power Query další prázdný záznam a vložte do řádku vzorců vzorec:

= [Jméno="Pavel", Přijmení="Lasák", Věk=42]

Další možnost jednoho záznamu (jména sloupců mohou obsahovat mezery):

= [Jméno osoby="Pavel", Příjmení osoby="Lasák", Věk osoby=42]

Případně s uvozovkami v datech:

= [Jméno osoby="Pavel", Příjmení osoby="Lasák", Věk osoby=42, Uvozovky test = """"]

Seznam (List) Záznamů (Records)

V předchozím, byl k vidění jen jeden záznam. Jak vytvořit více záznamů? Vložíte je do seznamu (Listu), Opět můžete přidat prázdný dotaz, pojmenujete (třeba SeznamZaznamu):

= {[Jméno="Pavel", Přijmení="Lasák", Věk=42], [Jméno="Jan", Přijmení="Malý", Věk=2]}

Speciální seznam záznamů

Co takhle zkusit do nového prázdného dotazu, do řádku vzorců vložit:

= [ POLE_1 = 1, POLE_2 = "A", POLE_3 = "{}", POLE_4 = {1,2,3}, POLE_5 = [A=1, B=2, C=3], POLE_6 = #table( {"A", "B"}, {{1,2},{3,4}} ), POLE_7 = (D) => D, POLE_8 = "Další pokusy """" " ]

Výsledek

Toto již přesahuje prostor pro tento článek, ale ukázka je k dispozici pro vaše testy.

Záznamy (Records) do tabulky

Vyberte například první záznam (Records) s jménem, příjmením a věkem.

= [Jméno="Pavel", Přijmení="Lasák", Věk=42]

Na pásu karet Převod klik na ikonu Do tabulky.

Obdržíte:

Podobně můžete převést do tabulky seznam záznamu, ale t již je opakování, seznam z předchozího převést umíte. A pak jen klik na dvojšipku...

Tabulka základ

Ukázka jak na tabulky pomocí M-kódu. Opět můžete přidat prázdný dotaz, ten pojmenovat tabulka a do řádku vzorců vložit:

= #table( {"Nazevy sloupce 1", "Název druhého sloupce"}, { {"První sloupec první řádek","Druhá sloupec první řádek"}, {"První sloupec druhý řádek","Druhý sloupec druhý řádek"}, {5,6} } )

Pro další ukázku a testy můžete vytvořit prázdný dotaz, který pojmenujete Tabulka2 a vložte do řádku vzorců:

= #table( type table [ #"Number Column"=number, #"Text Column"=text, #"Date Column"=date ], { {1,"Pavel",#date(2022,1,1)}, {2,"Lasák",#date(2022,12,31)} } )

V rozšířeném editoru tabulka vypadá (zobrazíte z menu Domů - Rozšířený editor):

Další ukázky jak na tabulku budou v samostatném článku.

Závěrem

Narazili jste na nějaké problémy, máte tip na vylepšení nebo doplnění článku, můžete se zmínit v komentářích.

Článek byl aktualizován: 14.01.2022 20:00

Pavel Lasák - autor webu

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

           

Komentáře zatím nejsou

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