Dynamické načítaní v PowerQuery přes jazyk M

Úvodem

Mám k dispozici tabulku s 12 listy nazvanými dle měsíců. V Excel potřebuji mít upravenou tabulku dle vybraného měsíce. Tak abych nemusel do PowerQuery vstupovat a daný měsís v M-kódu (M-code) měnit ručně (nebo načítat a upravovat 12x). Aneb jak dostat proměnou z listu v Excel elegantně přímo do PowerQuery (které na změnu zareaguje).

V totmo článku se dozvíte pro přehlednost je článek rozdělen na jednotlivé kapitoly.

• Zdrojová data
• Tabulka + Pomocná konstanta
• Načtení zdrojových dat z externího souboru
• Načtení a tvorba konstanty pro M-kód
• Aplikace dynamické konstanty v M-kódu
• Hotovo otestování
• Související články
• Ke stažení

Poznámka: Ve skutečnosti nepůjde jen o načtení tabulky, ale v daném měsíci se provede vícero úprav, případně může dojít i k volbě roku a například divize, jména zaměstnance a najednou již dynamické odkazy dávají větší a praktičtější smysl. V totmo článku jde o to ukázat jak jeden odkaz dostat do M-kódu. Podobně pak do Power Query vložíte další.

Zdrojová data

Mám zdrojovou tabulku (externí sešit), která obsahuje 12 měsíců, kdy každý měsíc je na samostatném listu. Já potřebuji načíst jen konktérní.

Tabulka + Pomocná konstanta

Vytvořím prázdný soubor, kde budu načítat data ze zdrojové tabulky (externí soubor). Tento soubor bude navíc obsahovat volbu měsíce, který chci načíst. Tak ať zbytek úprav může provést PowerQuery a dynamicky načte jen zvolený měsíc.

Prostě jen vytvoříte na listu výběrové pole, například využitím Ověření dat, nebo jen ručně napíšete do jednoho políčka název měsíce. Pro to, aby se vybraný měsíc dal následně načíst do PowerQuery, tak v druhé buňce uvedete, že jde o měsíc.

Podklad může vypadat například takto (Důležité sjou buňky B6 a B7):

Načtení zdrojových dat z externího souboru

Klasicky načtete přes Power Query data z Externího souboru a vyberete si třeba leden (následně vytvoříme dynamickou konstantu a propojíme).

Z pásu karet Data > Načíst data > Ze souboru > Ze sešitu

Najdete sešit (který chcete naimportovat a upravit) se zdrojovými daty a kliknete Importovat

V zobrazeném okně Navigátor si vyberete měsíc (třeba leden) a zvolíte Načíst > Načíst do... protože chci data načíst do stejného listu ať vidím změny (Pokud chcete v nové stačí Načíst > Načíst).

Protože mi dialogové okno nabízí kam se bude načítat Zvolím Tabulka (měla by být přednastavená) a zatrhnu Existující list (a nezapomenu určit do které buňky) a zbývá jen potvrdit OK.

A data jsou načtená (ať vidím na první dobrou, zda se načítá a funguje):

Podrobněji o načítání dat ze souboru jsem sepsal v samostatném článku.

Načtení a tvorba konstanty pro M-kód

Tabulka je připravená, ale určitě se vám nebude chtít těchto pár kroků opakovat pro každý měsíc (neřku-li pokud ještě načtenou tabulku nějak v PowerQuery upravujete, mažete a transformujete sloupce, atd.). Proto si připravíme dynamickou konstantu, kterou načteme do PowerQuery (podklady již máte to je onen měsíc).

V mám případě označím buňky B6:B7 (název konstanty a její hodnota).

Opět přes menu Data > Z tabulky nebo oblasti, v dialogové okně jen zkontroluji zda je zatrhnuto tabulka obsahuje záhlaví a kliknu na OK.

Otevře se okno PowerQuery a nás bude zajímat pravé menu Dotazy to rozklikneme a Přes F2 (nebo dvojklikem) načtenou tabulku přejmenujeme na Konstanta.

Z tabulky vytvoříme Odkaz. Přes pravé tlačítko nad názvem a zvolit Odkaz:

Na první pohled se může zdát, že došlo k duplikaci, ale na ten druhý v řádku vzorců vidíte rozdíl (je uvedeno):

= Konstatna

Tento vzoreček si upravíme

Syntaxe konstatny je jednoduchá:

Tabulka {Index řádku začiná se od 0}[Jmono sloupce]

Tak jen konstantu doplníme ať reaguje na data z tabulky

= Konstanta{0}[Měsíc]

Po dopsání stačí potvrdit Enter, ale to je jasné. Výsledkem bude název vybraného měsíce (a změna ikonky v dotazech):

Konstantu ještě můžete přejmenovat na: KonstantaMesic

Aplikace dynamické konstanty v M-kódu

Konstantu máte, teď jen zbývá jí vložit do M-kódu. V pravém okně pro tabulku měsíců vyberete krok, který rozhoduje o daném měsíci, který se načte:

Zbývá jen nahradit za onu konstantu (její název KonstantaMesic):

z = Zdroj{[Item="Leden",Kind="Sheet"]}[Data] přepíšete na: = Zdroj{[Item=KonstatnaMesic,Kind="Sheet"]}[Data]

Poznámka

Podobně pokud si přidáte další konstanty můžete využít na filtrování atd.

Hotovo otestování

Stačí provést zavřít a načíst (do nového listu se načte vybraná konstanta - tento list pak můžete skrýt).

Na Listu s tabulkou, když změníte název měsíce a provedete aktualizaci, načtou se data ze správného listu. Pokud tomu tak je, máte hotovo.

Pokud né? Máte listy správně pojmenovány, objevují se správné konstanty? Není někde překlep? Mezera navíc? Byla provedena aktualizace?

Aktualizovat, lze na pasu karet Data aktualizovat vše, nebo na Dotazech a připojení klikem na ikonku s aktualizací.

Samozdřejmě můžete mít více konstat atd. ale to již nechám na vaše testování.

Související články

Chcete se dozvědět o Power Query více doporučuji související články:

• Jak na PowerQuery - seriál

Ke stažení

Soubor ke stažení zdarma je v přípravě. Podpořit zveřejnění můžete podporou na Patreon.

Závěrem

Narazili jste na nějaké problémy, máte tip jak vylepšit, 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 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ář.