Dynamické dotazy v Power Query skrz parametry
- Vojtěch Šíma
- Mar 23
- 9 min read
Updated: Apr 12
tl;dr Power Query podporuje parametry – jednoduché, statické dotazy, které fungují jako globální konstanty. Umožňují snadno aktualizovat hodnoty napříč všemi dotazy. Life hack: v Power BI Service je můžeš měnit přímo v nastavení datového modelu, aniž bys musel otevírat samotný report. Tento článek ti ukáže praktické způsoby, jak s parametry pracovat.
Tabulka obsahu:
Introduction to Power Query Parameters:
Co je parametr v Power Query?
Examples:
Co je parametr v Power Query?
Parametry v Power Query jsou speciální objekty dotazů, které uchovávají hodnoty – buď jednu hodnotu, nebo seznam (například čísla, text, datumy atd.). Pomáhají dělat dotazy dynamičtějšími a snadněji udržovatelnými tím, že snižují potřebu pevně zadaných (hardcoded) hodnot. Můžeš je použít pro filtrování dat, nastavení cest k souborům nebo přepínání mezi prostředími. Tato funkce je ještě silnější v Power BI Service, kde lze hodnoty parametrů měnit přímo v nastavení datového modelu – není nutné otevírat report v Power BI Desktop. Prostě jednoduchý způsob, jak efektivně spravovat vstupy do dat.
Jak vytvořit parametr v Power Query?
V Power Query můžeš vytvořit parametr pomocí záložky Home a vybrání Manage Parameters → New. Případně, můžeš v sekci Queries/Dotazy kliknout pravým tlačítkem a vybrat New Parameter...
Pokud se na to cítíš, parameter se dá napsat i ručně skrz Blank Query.
Potom v okně Manage Parameter, po kliknutí New, vyplň požadované políčka,
Name, Description (volitelné, ale doporučuji vyplnit), Required, Type, Suggested Values, Current Value (případně Default Value).
Většina je jasná, jen dej pozor, co vybíráš při políčkách Type a Suggested Values.
Type: Určuje, jaký datový typ bude akceptovatelný pro tvoje hodnoty
Mega důležité: nikdy nenechávej parametr jako Any jelikož se ochuzuješ o funkcionalitu v Power BI Service, která ti dovolí parametr měnit. To samé platí pro Binary, nicméně tento typ pravděpodobně doborovlně nepoužiješ.
Suggested Values: Mění způsob, jak volíš Current Value:
Any value: Manuálně vložená jedna hodnota
List of values:
Seznam hodnot můžeš zadat ručně přes okno podobné funkci Enter Data
Uživatel může zadat i jinou hodnotu mimo seznam.
Dovoluje dodatečnou hodnotu Default Value, která slouží jako popis při najetí myší.
Uživel může volit hodnoty skrz dropdown menu v Power Query a zároveň v report sekci v Power BI Desktop skrz Edit Parameters.
Query:
Dovoluje použít existující List Query jako zdroj hodnot
Current Value je pořád manuální
Uživel může volit hodnoty skrz dropdown menu v report sekci v Power BI Desktop skrz Edit Parameters.
Tato možnost neposkytuje dropdown menu v Power Query
Poznámka: Power BI Service neumožňuje dropdown menu pro žádnou z variant
Jak upravit parametr v Power BI Service
Jakmile máš parametr nastavený a publikuješ svůj datový model (semantic model) do libovolného pracovního prostoru, můžeš hodnoty parametrů měnit v nastavení modelu, v sekci Parameters:
Poznámka: Power BI Service nenabízí rozbalovací menu (dropdown menu) pro výběr hodnoty parametru. Pokud nejde hodnota změnit (je zašedlá), změň její typ z Any nebo Binary na jiný přímo v Power Query.
To je vše k úvodu—teď mrkneme na nějaké konkrétní scénáře z praxe.
Synchronizovaný datumový filtr napříč dotazy
Podívejme se na pár reálných příkladů. Pomocí parametru years_to_load si můžeme nastavit parametrizovaný časový rozsah, který se bude používat ve všech dotazech—je snadno upravitelný a plně centralizovaný.
Chceme pracovat s časovým rozsahem. Okáčko. Co k tomu potřebujeme vlastně? Pojďme vytvořit jednotlivé části jako jednoduché dotazy využívající tento parametr.
startDate
Tento dotaz nám určí začátek časového rozsahu. Vytvoř nový prázdný dotaz a vlož do něho tento krátký kód:
= Date.From( Date.StartOfYear( Date.AddYears( DateTime.LocalNow(), -years_to_load ) ) )Jak kód pracuje krok po kroku:
Získá aktuální datum a čas (např. 22. 3. 2025 + čas).
Odečte počet let zadaný v parametru (např. 3), výsledkem je 22. 3. 2022 + čas.
Upraví datum na začátek daného roku, tedy 1. 1. 2022 + čas.
Převede hodnotu z typu datetime na typ date, čímž odstraní časovou složku.
Finální výsledek: #date(2022, 1, 1) — čistá datumová hodnota.
endDate
Tento dotaz určí konec našeho časového rozsahu. Vytvoř nový prázdný dotaz a vlož tento jednořádkový kód:
= Date.From( Date.EndOfYear( DateTime.LocalNow() ) )Tento dotaz se provádí krok po kroku následovně:
Získá aktuální datum a čas (např. 22. 3. 2025 + čas).
Upraví datum na konec aktuálního roku, výsledkem je 31. 12. 2025 + čas.
Převede hodnotu z typu datetime na typ date, čímž odstraní časovou složku.
Finální výsledek: #date(2025, 12, 31) — čistá datumová hodnota.
Implementace do dotazu
Nyní máme vše potřebné a směle to můžeme napráskat do našich dotazů. Pokud používáš krok Filtered rows (Filtrované řádky) pro nastavení rozsahu dat, jednoduše do něj přidej náš rozsah pomocí parametru.
= Table.SelectRows(previousStep, each [date]>= startDate and [date]<=endDate )Pokud si píšeš svoje SQL, můžeš rozsah přidat takto:
"select
column1,
column2
from tbl
where date_column between '" & Date.ToText(startDate, "yyyy-MM-dd") & "' and '" & Date.ToText(endDate, "yyyy-MM-dd") & "'"Datum převádím na text pomocí pevně daného formátu, který je široce podporovaný většinou SQL enginů. Případně si můžeš druhý parametr ve funkci Date.ToText upravit podle svých potřeb.
A to je celé! Pokud chceš jít ještě o krok dál, můžeš si z toho udělat funkci, ale i v této podobě ti to pokryje většinu běžných scénářů. A co je nejlepší — když bude chtít uživatel vidět data za více nebo méně let, stačí změnit parametr v Power BI Service. Při dalším refreshi se rozsah dat automaticky aktualizuje.
Filtrování stejného seznamu hodnot napříč dotazy
Další skvělý příklad, kde se hodí centralizace filtrů pomocí parametrů, je situace, kdy chceš aplikovat stejný filtr v celém reportu – včetně všech poddotazů. Například pokud chceš pracovat pouze s produkty A, B a C napříč všemi produktově orientovanými dotazy. Podívejme se, jak to můžeš nastavit.
Vytvoř parametr
Začneme vytvořením parametru, který bude uchovávat informace o produktech a zároveň řídit všechny související dotazy. Může se zdát logické vytvořit parametr jako seznam hodnot – nicméně parametry v Power BI mohou mít pouze jednu aktuální hodnotu (Current Value), takže tento přístup nebude fungovat.
Místo toho si seznam postavíme jako textový řetězec.
Pojďme inicializovat parametr jako typ Text, s možností Libovolná hodnota (Any value), například takto:
Transformuj parametr
Teď, když máme parametr připravený, musíme udělat pár úprav, abychom ho mohli efektivně použít jako filtr. Ukážu ti dva přístupy: jeden pro použití se standardní funkcí List.Contains a druhý pro použití v SQL pomocí klauzule IN.
Jde o univerzální postup, který si můžeš zabalit do vlastní funkce a znovu použít u všech parametrů, které slouží k filtrování.
List.Contains možnost
Otevři nový dotaz/query pomocí Blank Query a nakopíruj následující:
= List.Transform( Text.Split(products, ","), each Text.Trim(_) )This query executes step by step as follows:
Rozdělení parametru product podle čárky.
Tento krok vytvoří seznam, kde každý prvek obsahuje hodnotu mezi jednotlivými oddělovači.
Transformace nového seznamu a ořezání mezer na začátku a konci.
Tento krok je volitelný, ale zajistí, že v seznamu nebudou žádné zbytečné mezery – bez ohledu na to, jestli byly hodnoty zadány s mezerami nebo bez.
(Volitelně) Další čištění dat podle potřeby, například pomocí Text.Clean nebo převod na velká písmena pomocí Text.Upper.
SQL IN možnost
Otevři nový dotaz/query pomocí Blank Query a nakopíruj následující:
= Text.Combine( List.Transform(products_filter, each "'" & _ & "'"), ",")This query executes step by step as follows:
Znovu použijeme dříve vytvořený očištěný seznam (nebo ho sestavíme z parametru).
Každou položku obalíme do jednoduchých uvozovek pomocí transformačního kroku.
Spojíme celý seznam do jednoho textového řetězce odděleného čárkami.
Každá hodnota je oddělena čárkou, přičemž za poslední položkou čárka není.
Výsledkem je čistý textový řetězec, ideální pro použití v SQL klauzuli IN.
To samé, ale jako obrázek
Implementace do dotazu
Nyní máme vše potřebné a směle to můžeme napráskat do našich dotazů. Pokud používáš krok Filtered rows (Filtrované řádky) pro filtrování produktů, jednoduše do něj přidej seznam produktů pomocí parametru.
= Table.SelectRows(previousStep, each List.Contains( products_filter, [product] ) )Pokud si píšeš svoje SQL, můžeš rozsah přidat takto:
"select
column1,
column2
from tbl
where product_column in ("& products_filter_sql &")
Spravuj definici připojení k zdrojům přes parametry
Dalším skvělým příkladem využití parametrů je jejich použití pro definici připojovacího řetězce k celému datovému modelu. Při vývoji reportů napříč vývojovým (dev) a produkčním (prod) prostředím může být ruční přepínání každého dotazu dost otravné. Místo toho můžeš použít Seznam hodnot (List of Values) s předdefinovanými připojovacími řetězci a snadno tak přepínat mezi prostředími.
Vytvoř parametr
Nakliej parametr jako hodnoty typuText pomocí List of Values:
Implementace do dotazu
Implementace je docela přímočará. Můžeš použít jakoukoli funkci, která ti umožní definovat zdroj – stačí, aby byla zadaná hodnota platná. V našem případě se připojujeme k databázi SQL Server, kde název hostitele (serveru) určujeme pomocí parametru. Zároveň chceme spustit dříve vytvořený SQL dotaz – a tady je, jak to může vypadat:
sqlQuery = Value.NativeQuery(
Sql.Database(sql_server_environments, "database"), filterProductSQL, [
EnableFolding = true
]
)
V tomto kódu proměnná sql_server_environments obsahuje informace o hostiteli a je nastavena pomocí parametru. Proměnná filterProductSQL, kterou jsme definovali dříve, využívá další parametr pro zpřesnění filtrování dat.
Když se na kód podíváme blíž, můžeme ho ještě vylepšit. Momentálně je název databáze napsán napevno jako "database" – to se dá "fxinout" tak, že k jednotlivým prostředím přiřadíme odpovídající databázi, a tím centralizujeme správu na jednom místě.
Vytvořme si nový prázdný dotaz, ve kterém definujeme, která databáze patří ke kterému prostředí. Můžeš použít možnost Enter Data, ale pokud si chceš napsat do CVčka, že jsi profík v Mku, můžeš to napsat i ručně:
Vyrobil jsem pro tebe dvě varianty, obě vykouzlí to samé, jenom trošku jinak:
let
// varianta se seznamem záznamů (record) – vyfiltruješ požadované prostředí a získáš pole 'db' z prvního záznamu
definition =
{
[env = "prod-sqlserver.com", db = "prod_db"],
[env= "dev-sqlserver.com", db = "dev_db"]
},
returnCurrentDatabase = List.Select(definition, each _[env]=sql_server_environments){0}[db]
in
returnCurrentDatabase
let
// varianta s jedním záznamem, kde každý název pole je název prostředí a jeho hodnota je název databáze – pak jen vybereš správné pole
envMapping = [
#"prod-sqlserver.com" = "prod_db",
#"dev-sqlserver.com" = "dev_db"
],
currentDatabase = Record.Field(envMapping, sql_server_environments)
in
currentDatabasePokud jsi použil(a) možnost Enter Data, nezapomeň použít Table.SelectRows pro vyfiltrování správného prostředí a následně převést výsledek na jednu hodnotu – stejně jako v první variantě.
Teď pojďme upravit naši původní funkci Sql.Database, aby využívala nový, automatický a centralizovaný způsob určování správné databáze pro každé prostředí.
sqlQuery = Value.NativeQuery(
Sql.Database(sql_server_environments, sql_server_database), filterProductSQL, [
EnableFolding = true
]
)
Zvážení a alternativy
Toto řešení má jednu očividnou nevýhodu – názvy databází jsou definovány přímo uvnitř reportu a ne přes parametry. Ale stejně jako jsme to udělali u nastavení produktů, můžeš tento přístup přepsat pomocí List of Values (Seznam hodnot), kde bude každý záznam jako textový řetězec oddělený čárkou, např.: prod-sqlserver.com, prod_db atd. Následně můžeš použít stejné dělení a transformační techniky k extrakci hodnot.
Jak už to bývá – existuje víc řešení pro stejný problém, tak si vyber to, které nejlépe sedí tvému scénáři.
Parametry v Power BI Deployment Pipeline Rules
Tato část článku se nezaměřuje na Power BI Deployment Pipelines jako celek, ale konkrétně na to, jak využít parametry v takzvaných Deployment Pipeline Rules.
Pokud máš předplatné Fabric, můžeš díky Deployment Pipelines jednodušeji nasazovat svoje reporty do různých prostředí. Jedna z výhod je, že když už máš v sémantickém modelu nastavené parametry, můžeš přes Deployment Pipeline Rules měnit jejich hodnoty při nasazení do další fáze – třeba do Test nebo Production.
Jak nastavit pravidla (rules)
Jakmile máš vytvořené tři pracovní pstory (workspaces) jako součást pipeline, otevři si jeden z nich a klikni na View Deployment Pipeline.
Tady si vyber prostředí, pro které chceš pravidlo nastavit, a dole klikni na ikonu blesku.
Uvidíš všechny Fabric položky, na které můžeš pravidla nastavit. Z vlastní zkušenosti – nově přidané věci tam hned neuvidíš. Musíš je nejdřív aspoň jednou nasadit, a pak se objeví pro nastavení pravidel.
Jakmile klikneš na konkrétní položku, můžeš si vybrat mezi Data source rules a Parameter rules. Obecně platí, že Data source rule ti dovolí změnit zdroj dat (pokud to daný zdroj umožňuje). Parameter rules umí něco podobného, ale jsou flexibilnější – můžeš s nimi upravovat nejen zdroj dat, ale třeba i filtry a další věci.
My chceme upravit Parameter rules, takže na ně klikni. Uvidíš dostupné parametry a jejich aktuální hodnoty. Když klikneš na Other, můžeš zadat novou statickou hodnotu. Zadej, co potřebuješ, klikni na Save – a je hotovo.
Podobně jako u parametrů v Power BI Service, ani tady není žádné skutečné rozbalovací menu – i když máš parametr nastavený jako seznam hodnot. Dropdownmenu je spíš takové ilustrační – ukáže původní hodnotu (Current Value), možnost Other, a tvoji poslední zadanou hodnotu (pokud se liší od původní "Current Value").
A to je vlastně všechno. Kdykoli pak nasadíš sémantický model do tohohle prostředí, hodnota parametru se automaticky změní. Pravidlo můžeš kdykoli změnit nebo smazat.
A pokud si to chceš otestovat, přejdi do cílového workspacu a ručně aktualizuj sémantický model. Nasazení totiž samo o sobě data neaktualizuje– na to nezapomeň.
Závěr
Parametry v Power Query jsou parádní způsob, jak udělat reporty flexibilnější a mnohem jednodušší na správu. Ať už synchronizuješ filtry podle dat, opakovaně používáš filtry na produkty napříč dotazy, nebo přepínáš mezi dev a prod prostředím bez zbytečného přepisování – parametry ti s tím pomůžou. A bonus? V Power BI Service je můžeš upravit přímo v nastavení datového modelu, takže není potřeba otevírat samotný report. V tomhle článku jsme si ukázali reálné scénáře, jak s nimi pracovat – trocha teorie, ale hlavně použití z praxe. Díky tomu si udržíš řešení přehledné, centralizované a hlavně bez zbytečné ruční práce.
Comments