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

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

Dynamické hodnoty přes parametr v PowerQuery - jazyk M

Videokurzy Excel

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

Jak na dynamické načítání dat v Power Query. Aneb jak upravit načtení nebo filtraci tabulky využitím parametru.

Pokud vás nezaujme konstanta případně přes funkci.

Předpokládám základní povědomé i PowerQuery a umění načíst data ze sešitu.

Dynamické hodnoty - teorie

V Power Query je mnohdy potřeba prováděné úpravy uživatelsky, nebo-li upravovat "kastomivat" načítané data dle volby uživatele. Například filtrovat dle uživatele. Načítat jen požadovaný list. Zobrazovat jen požadované sloupce. Jenže uživatel se PowerQuery bojí. Proto potřebujete do Power Query vkládat uživatelské hodnoty z Excelu (který uživatel důvěrně zná). Tohle lze provést několika způsoby:

Dynamické hodnoty využitím funkce

V tomto článku se představím jak funkce v Power Query se podívá do tabulky jako tabulka na patřičný řádek a z druhého sloupce převezme hodnotu (v ukázkce adresář alias složku, název souboru a také název listu). Z důvodu přehlednosti je článek rozdělen na několik kapitol:


Zdrojová data

Základem jsou dva sešity. Jeden z nich bude zdrojová tabulka (ze který se bude dynamicky načítat), druhý cílový sešit ve kterém si vyberete/vyplníte například název listů, adresář, název měsíc a přes Power Query se načte příslušný list z vybraného souboru ve vybraném adresíáři. Nebo se vyfiltruje hodnota z příslušného sloupce, atd.

Tabulka tako tabulka - zdrojová data

Potřebujeme zdrojovou tabulku, ze které si funkce vytvořena v M-kódu bude přebírat informace. Pro správnou funkci musí být tabulka "naformátována" jako tabulka. V tabulce budou dva sloupce. První bude označovat o co jde, nebo-li typ hodnoty ("NazevSouboru") a ve druhém sloupci bude hodnota pro onen parametr ("MujSoubor.xlsx"). Další řádek může být složka ("Adresář" - "C:/test/"), další název listů ("List" - "Leden"), atd.

Samozřejmě, že tabulka jako tabulka bude mít své jméno ("TabulkaParametru"). Nebude se jmenovat Tabulka1 (ale pokud vám nevadí tak klidně může).

Power Query - Excel - dynamické načtení hodnoty

Načtení externích dat

Budu načítat sešit který obsahuje listy s názvy měsíců. Na každém listu je stejná tabulka, mající stejné názvy sloupců jen hodnoty jsou jiné, příslušející daným měsícům.

Poznámka: Pokud nebudete potřebovat načítat jednotlivé listy a bude vám stačit pouze jeden konkretní list, nevadí (tj. jen budete dynamicky měnit název souboru a jméno adresáře). Postup je stejné, jen v posledním kroku vynecháte z návodu odkaz na dynamický název na konkrétní list.

Power Query - Excel - zdrojová data

Funkce v M s parametrem

Potřebuji napsat funkci, která se bude dotazovat do tabulky jako tabulka v listu Excelu a dle zadaného parametru vrátí hodnotu z dalšího sloupce. Funkce je jednoduchá, ale než ji napíšete potřebujete přidat a přejmenovat prázdný dotaz.

Prázdný dotaz

V Power Query v menu Domovská stránka (Home) klik na Nový zdroj > Jiné zdroje > Prázdný dotaz

Přidá se prazdný dotaz, který přejmenujete na: f_Parametr (opět jméno může být jakekoliv, ale já si zvolil zrovna toto).

Power Query - Excel - prazdný dotaz s funkcí

Rozšířený editor

Prázdný dotaz máte a má i své jméno. Teď jen do něj vložit kód. Protože kód se nevejde na jeden řádek je potřeba zobrazit rozšířený editor a vložit do nej kód (stačí Copy > Paste). Jen na menu Domovská stránka (Home) klik na Rozšířený editor:

Power Query - Excel - prazdný dotaz s funkcí

Kód, funkce:

A teď do prázdného dotazu vložíte funkci:

// Funkce vratí hodnotu z tabulky let Parameter=(ParameterLabel) => let // Vnořená funkce, který vrátí požadovanou hodnotu do tabulky // na základě parametru ze sloupce a hodnoty v dalším sloupci Source = Excel.CurrentWorkbook(){[Name="TabulkaParametru"]}[Content], value = Source{[ParameterZeSloupce=ParameterLabel]}[PrislusnaHodnotaTabulka] in value in Parameter Power Query - Excel - prazdný dotaz s funkcí

Funkce vylepšena

Funkce se dá vylepšit i o volbu jména tabulky jako tabulka:

// funkce s více parametry let Parameter=(TabulkaJmeno, ParameterLabel) => let // ParametrTable Source = Excel.CurrentWorkbook(){[Name=TabulkaJmeno]}[Content], value = Source{[ParameterZeSloupce=ParameterLabel]}[PrislusnaHodnotaTabulka] in value in Parameter

Mám schováno pro další tipy a triky s dynamickým načítáním dat z tabulek v Excel pro M-language. Časem možná přidám další tipy a triky.

Vložení funkce do M kódu pro načítanou tabulku

Nejprve si načtěte data do PowerQuery, ať už ze souboru s více listy, nebo jen jedno tabulku ze sešitu atd. Pokud nevíte jak doporučuji si projít články ve kterých jsem popsal:

Předpokládám, že tabulku máte načtenou. Zbývvá se podívat do kódu který se načetl:

Načtený kód

Stojíte v dotazech na načtené tabulce a jen na menu Domovská stránka (Home) klik na Rozšířený editor a obdržíte podobný kód (záleží na provedených úpravách):

let Zdroj = Excel.Workbook(File.Contents("D:\Skoleni\Excel Pokrocile\09 - PowerQuery\Power Query - dynamicky data zdroj.xlsx"), null, true), #"Odebrané sloupce" = Table.RemoveColumns(Zdroj,{"Name", "Item", "Kind", "Hidden"}), #"Rozbalené Data" = Table.ExpandTableColumn(#"Odebrané sloupce", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}), #"Záhlaví se zvýšenou úrovní" = Table.PromoteHeaders(#"Rozbalené Data", [PromoteAllScalars=true]), #"Změněný typ" = Table.TransformColumnTypes(#"Záhlaví se zvýšenou úrovní",{{"Id", type any}, {"Jméno", type text}, {"Měsíc", type text}}), #"Filtrované řádky" = Table.SelectRows(#"Změněný typ", each ([Id] = 1 or [Id] = 2 or [Id] = 3) and ([Měsíc] = "Leden")) in #"Filtrované řádky" Power Query - Excel - načtená tabulka - staticky

Upravený kód

Tabulku máte načtenou a jen statické informace například o adresáři a souboru "D:\Skoleni\Excel Pokrocile\09 - PowerQuery\Power Query - dynamicky data zdroj.xlsx" upravíte na f_Parameter("Slozka")&f_Parameter("Jmeno").

Neboli f_Parameter je název funkce, kterou jste vytvořily. Jméno je název řádku a funkce vrátí název souboru. Znak & spojí adresář a název souboru. Ukázka pro jeden řádek:

Zdroj = Excel.Workbook(File.Contents("D:\Skoleni\Excel Pokrocile\09 - PowerQuery\Power Query - dynamicky data zdroj.xlsx"), null, true), // upravíte na Zdroj = Excel.Workbook(File.Contents(f_Parameter("Slozka")&f_Parameter("Jmeno")), null, true),

Podobně upravíte pro měsíc, atd.

Výsledný M-kód může pak vypadat například takto:

let Zdroj = Excel.Workbook(File.Contents(f_Parameter("Slozka")&f_Parameter("Jmeno")), null, true), #"Odebrané sloupce" = Table.RemoveColumns(Zdroj,{"Name", "Item", "Kind", "Hidden"}), #"Rozbalené Data" = Table.ExpandTableColumn(#"Odebrané sloupce", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),Leden_Sheet = Zdroj{[Item=f_Parameter("ListJmeno"),Kind="Sheet"]}[Data], #"Záhlaví se zvýšenou úrovní" = Table.PromoteHeaders(#"Rozbalené Data", [PromoteAllScalars=true]), #"Změněný typ" = Table.TransformColumnTypes(#"Záhlaví se zvýšenou úrovní",{{"Id", type any}, {"Jméno", type text}, {"Měsíc", type text}}), #"Filtrované řádky" = Table.SelectRows(#"Změněný typ", each ([Id] = 1 or [Id] = 2 or [Id] = 3) and ([Měsíc] = f_Parameter("ListJmeno"))) in #"Filtrované řádky"

Výsledek

Máte-li stačí se jen vrátit do Excel a v tabulce jako tabulka nastavit požadované parametry a nad cílovou tabulkou pravý klik a vybrat obnovit. Pokud se načtou správné údaje máte hotovo.

Power Query - Excel - načtená tabulka - staticky

Související články

Chcete se dozvědět více? Doporučuji související články:

Microsoft Excel VBA - stahuj logo

Ke stažení

Soubory ke stažení zdarma jsou v přípravě pro publikaci. Podpořit a zrychlit jejich zveřejnění můžete podporou na Patreon (
partneři již ukázky souboru mají jako poděkování
).


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: 25.11.2019 14:49

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






Excel


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

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