- Power Query načítá a upravuje data: sloučí například dvanáct měsíčních exportů do jedné tabulky, odstraní prázdné řádky, rozdělí sloupce a spojí tabulky podle klíče.
- Každý krok se zapíše v jazyce M, takže se celá úprava příště zopakuje tlačítkem Aktualizovat, bez ručního kopírování.
- Stejný Power Query najdete v Excelu, v Power BI Desktop i ve Fabricu jako Dataflow Gen2.
- Na pozadí stojí kód v jazyce M, který můžete naklikat, napsat ručně nebo nechat vygenerovat.
Obsah 6 částí
- 01Power Query, co to je
- 02Power Query v Excelu
- 03Power Query v Power BI
- 04Jazyk M
- 05Kde Power Query v Excelu přestává stačit?
- 06Časté otázky
Power Query, co to je
Power Query od Microsoftu připravuje data pro další práci. Načte je ze zdroje, upraví je a výsledek uloží do modelu v Power BI či do tabulky v Excelu. Zdrojem může být soubor CSV, sešit Excelu, celá složka exportů, databáze SQL Server, SharePoint, webová stránka nebo API.
Úpravy se v editoru běžně dělají klikáním: odstranit sloupec, filtrovat řádky, změnit datový typ, sloučit dvě tabulky. Každé kliknutí se uloží jako krok a kroky se seřadí do seznamu Použité kroky. Když příští měsíc přijdou nová data, Power Query projde stejné kroky znovu, sám a ve stejném pořadí.
Účetní každý měsíc stáhne z ERP export prodejů jako CSV. Soubory ukládá do jedné složky. Power Query načte celou složku, soubory spojí pod sebe, odstraní hlavičky navíc, opraví formát data a přidá sloupec s měsícem. Když přibude třináctý soubor, stačí kliknout na Aktualizovat.
Power Query v Excelu
V Excelu pro Windows je Power Query vestavěný od verze 2016 a v Microsoft 365. Najdete ho na kartě Data pod tlačítkem Načíst data. Do Excelu 2010 a 2013 se instaloval jako samostatný doplněk.
Výsledek dotazu můžete poslat na list jako tabulku, uložit jen jako připojení, nebo nahrát do datového modelu Excelu, známého jako Power Pivot. List má strop 1 048 576 řádků. Datový model tímto stropem omezený není, zvládne miliony řádků a staví se nad ním kontingenční tabulky.
Power Query v Power BI
V Power BI Desktop se Power Query otevře tlačítkem Transformovat data. Zjednodušeně řečeno, každá tabulka v modelu Power BI prošla dotazem Power Query, i když ho autor jen odklikl při načtení.
Po publikaci do Power BI Service běží stejné dotazy při každé plánované aktualizaci. Když data leží na serveru nebo disku ve vaší síti, potřebuje Power BI Service k dotazům on-premises data gateway.
Power Query je primárně určený pro klikání a má hned několik způsobů, jak naklikané kroky přepsat do kódu, se kterým si zdroj poradí lépe a rychleji. Například query folding dokáže některé kroky přepsat do SQL dotazu, který se pošle rovnou do databáze, a celý refresh se tím zrychlí.
Kde Power Query najdete
| Kde | Kam výsledek míří | Jak se obnovuje |
|---|---|---|
| Excel | List, nebo datový model Excelu | Ručně tlačítkem Aktualizovat vše |
| Power BI Desktop | Sémantický model | Ručně, po publikaci plánovaný refresh v Service |
| Dataflow Gen2 ve Fabricu | Lakehouse, Warehouse a další cíle | Plánovaně nebo z pipeline |
Jazyk M
Každé kliknutí v editoru Power Query zapíše jeden řádek kódu v jazyce M. Kód uvidíte v Rozšířeném editoru: dotaz začíná slovem let, pod ním je seznam pojmenovaných kroků a na konci za slovem in stojí krok, jehož výsledek se načte. Jeden krok může vypadat třeba jako Table.SelectRows(Zdroj, each [Částka] > 0).
Kliknutí v editoru a řádek v jazyce M jsou dvě podoby stejného kroku. Co odkliknete, můžete přečíst v kódu, a co napíšete v kódu, uvidíte jako krok v seznamu.
Na běžnou práci stačí editor. Ruční psaní M se vyplácí ve chvílích, kdy klikání nestačí:
- vlastní funkce, která použije stejné kroky na padesát souborů nebo tabulek,
- parametry, třeba cesta ke složce nebo datum, od kterého se data načítají,
- načtení z REST API, včetně stránkování a přihlášení,
- práce s chybami, aby jeden špatný řádek neshodil celý refresh.
Jazyk M doporučuji naučit se každému, kdo nechce být limitovaný grafickým rozhraním a nabídkou podporovaných konektorů, nebo kdo chce kroky konsolidovat a zlepšit tím průběh celého workflow.
Kde Power Query v Excelu přestává stačit?
Power Query v Excelu se sám neobnoví. Někdo musí sešit otevřít a kliknout na Aktualizovat. Plánovaný refresh přinese až Power BI Service nebo Dataflow Gen2 ve Fabricu, případně automatizace přes Power Automate. Obnovu může spustit i vlastní naplánovaný skript, průvodce ale počítá s cestou bez programování.
Sešit s dotazy navíc zůstává souborem. Kolega s kopií z minulého týdne má jiná čísla a výpočty postavené nad výsledkem žijí ve vzorcích každého sešitu zvlášť. Až se stejný výkaz skládá pro víc lidí, převezme ho Power BI se stejnými dotazy Power Query, jen publikovanými na jedno místo. Kde přesně hranice leží, rozebírá průvodce Power BI, nebo Excel.
Časté otázky
Je Power Query součástí Excelu?
V Excelu pro Windows od verze 2016 a v Excelu z Microsoft 365 ano, najdete ho na kartě Data. Do Excelu 2010 a 2013 se instaloval jako doplněk. Excel pro Mac má Power Query také, s menším výběrem zdrojů dat.
Jaký je rozdíl mezi Power Query a Power Pivot?
Power Query data načte a upraví. Power Pivot je datový model Excelu, který drží upravené tabulky, vazby mezi nimi a výpočty v jazyce DAX. Obvykle spolu pracují: Power Query plní model a nad modelem stojí kontingenční tabulky. Power BI používá stejnou dvojici.
Je Power Query v Excelu stejný jako v Power BI?
Jazyk M i editor jsou téměř stejné, takže dotaz jde zkopírovat z jednoho do druhého. Liší se výběr konektorů a místo, kam výsledek míří: v Excelu na list nebo do datového modelu, v Power BI do sémantického modelu, který se v Service obnovuje podle plánu.
Musím umět programovat, abych Power Query používal?
Většinu práce odkliknete v editoru a kód v jazyce M se napíše sám. Znalost M se hodí na vlastní funkce, načítání z API a hledání pomalých kroků. Začít se dá bez ní.
Co je query folding?
Query folding znamená, že Power Query přeloží vaše kroky do jazyka databáze a nechá databázi spočítat filtry a součty. Do Power Query pak přijde jen výsledek a refresh je rychlejší. Když některý krok přeložit nejde, zbytek práce dělá Power Query sám a refresh se zpomalí.
Jak jste na tom vy?
Jazyk M v Power Query
83 000 Kčza skupinu, bez DPH
Pro ty, komu v Power Query nestačí klikání: syntaxe M, vlastní funkce, konektory, načítání z REST API a práce s chybami.
