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

Jste zde: Úvodní stránka » excel » Query-editor » Dynamicke-nacitani-PowerQuery-jazyk-M
Microsoft Excel logo

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

Videokurzy Excel

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

Potřebujete-li v PowerQuery dynamicky měnit načítané listy? Jak elegantně využít jazyk M a dynamický odkaz s dozvíte v tomto článku.

Předpokládám mírně pokročilou znalost Excel. kdy pár tabulek zpracovaných (otevřených) v PowerQuery máte za sebou.

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

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

Zdrojové data - Power Query - dynamické odkazy Excel

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

Pomocná tabulka Power Query - dynamické odkazy Excel

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

Nactena data - Power Query - dynamické odkazy Excel

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.

prejmenovaná tabulka - Power Query - dynamické odkazy Excel

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] konstanta tabulka - Power Query - dynamické odkazy Excel

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

konstanta tabulka - Power Query - dynamické odkazy Excel

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:

Power Query - dynamické odkazy Excel

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] Power Query - dynamické odkazy Excel

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

Power Query - dynamické odkazy Excel

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:

Microsoft Excel VBA - stahuj logo

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: 07.11.2019 14:40

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ář.







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 | 108

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