Osvědčené postupy při práci s Power Query

Tyto Power Query osvědčené postupy vám pomůžou zlepšit výkon dotazů, využít posouvání dotazů, vybrat správné datové typy, uspořádat transformace a znovu použít logiku s parametry a vlastními funkcemi. Platí pro prostředí Power Query Desktop i Power Query Online.

Volba správného konektoru

Power Query nabízí mnoho datových konektorů. Tyto konektory se liší od zdrojů dat, jako jsou TXT, CSV a Excel soubory, až po databáze, jako jsou Microsoft SQL Server, a oblíbené produkty saaS (software jako služba), jako jsou Microsoft Dynamics 365 a Salesforce. Pokud není v okně Získat data k dispozici účelový konektor, použijte obecný konektor, jako je ODBC nebo OLE DB.

Zvolte účelový konektor pro váš zdroj dat, pokud je k dispozici. Konektor SQL Server například poskytuje lepší prostředí pro získání dat než obecný konektor ODBC při připojování k SQL Server databázi. Konektor SQL Server také podporuje funkce pro zvýšení výkonu, jako je skládání dotazů. Další informace najdete v tématu Přehled vyhodnocení dotazů a posouvání dotazů v Power Query.

Každý datový konektor se řídí standardním prostředím, jak je vysvětleno v části Získávání dat. Toto standardizované prostředí má fázi nazvanou Náhled dat. V této fázi máte k dispozici uživatelsky přívětivé okno pro výběr dat, která chcete získat ze zdroje dat, pokud to konektor umožňuje, a jednoduchý náhled dat těchto dat. V okně Navigátor můžete dokonce vybrat více datových sad ze zdroje dat.

Poznámka:

Pokud chcete zobrazit úplný seznam dostupných konektorů v Power Query, přejděte do konektorů v Power Query.

Filtrování dat v rané fázi za účelem zlepšení výkonu

Filtrujte data co nejdříve, abyste snížili počet řádků, které Power Query zpracovává během následných transformací. U konektorů, které podporují posouvání dotazů, Power Query můžou odesílat filtry zpět do zdroje dat, jak je popsáno v tématu Přehled vyhodnocení dotazů a posouvání dotazů v Power Query. Filtrování irelevantních dat omezuje také data zobrazená v náhledu dat.

Pomocí nabídky automatického filtrování, která zobrazuje jedinečný seznam hodnot nalezených ve sloupci, vyberte hodnoty, které chcete zachovat nebo odfiltrovat. Pomocí panelu hledání můžete najít hodnoty ve sloupci.

Můžete také využít filtry specifické pro typ, například v předchozím pro sloupec data, data a času nebo dokonce časového pásma.

Tyto filtry specifické pro typ vám můžou pomoct vytvořit dynamický filtr, který vždy načítá data, která jsou v předchozím x počtu sekund, minut, hodin, dnů, týdnů, měsíců, čtvrtletích nebo letech.

Poznámka:

Další informace o filtrování dat na základě hodnot ze sloupce najdete v části Filtrovat podle hodnot.

Provádějte náročné operace až nakonec, aby se zlepšil výkon

Pokud chcete zvýšit výkon náhledu v editoru Power Query, proveďte nákladné operace jako poslední. Některé operace vyžadují čtení celého zdroje dat, aby se vrátily všechny výsledky, a proto jsou pomalé ve verzi Preview. Pokud například provedete řazení, je možné, že prvních několik seřazených řádků je na konci zdrojových dat. Pokud chcete vrátit všechny výsledky, musí operace řazení nejprve číst všechny řádky.

Jiné operace (například filtry) před vrácením výsledků nemusí číst všechna data. Místo toho pracují s daty podle toho, čemu se říká "streamování". Data proudí a výsledky se vracejí během tohoto procesu. V editoru Power Query musí tyto operace číst jenom dostatek zdrojových dat k naplnění náhledu.

Pokud je to možné, nejprve proveďte takové operace streamování a dražší operace až nakonec. Provádění operací v tomto pořadí pomáhá minimalizovat dobu strávenou čekáním na vykreslení náhledu při každém přidání nového kroku do dotazu.

Použití podmnožiny dat při vytváření dotazu

Pokud je přidání nových kroků v editoru Power Query pomalé, omezte data zpracovávaná během vývoje dotazu pomocí funkce Zachovat první řádky. Po přidání všech požadovaných kroků odeberte krok Zachovat první řádky , aby dokončený dotaz zpracuje úplnou sadu dat.

Použití správných datových typů

Nastavte správný datový typ pro každý sloupec, aby Power Query mohl zpřístupnit transformace a filtry specifické pro typ. Když například vyberete sloupec kalendářního data, můžete použít možnosti ve skupině sloupců Datum a čas v nabídce Přidat sloupec . Pokud sloupec nemá nastavený datový typ, jsou tyto možnosti neaktivní.

Podobná situace nastane u filtrů specifických pro typ, protože jsou specifické pro určité datové typy. Pokud sloupec nemá definovaný správný datový typ, tyto filtry specifické pro daný typ nejsou k dispozici.

Je důležité, abyste vždy pracovali se správnými datovými typy pro sloupce. Při práci se strukturovanými zdroji dat, jako jsou databáze, se informace o datovém typu přenesou ze schématu tabulky nalezeného v databázi. U nestrukturovaných zdrojů dat, jako jsou soubory TXT a CSV, je ale důležité nastavit správné datové typy pro sloupce pocházející z tohoto zdroje dat. Power Query ve výchozím nastavení nabízí automatické zjišťování datových typů pro nestrukturované zdroje dat. Další informace o této funkci a jak vám může pomoci v datových typech.

Poznámka:

Další informace o důležitosti datových typů a o tom, jak s nimi pracovat, najdete v části Datové typy.

Profilování a zkoumání dat

Než připravíte data a přidáte kroky transformace, povolte nástroji pro profilaci dat Power Query zjistit informace o vašich datech.

Power Query poskytuje tři nástroje pro profilaci dat:

nástroj	Co ukazuje
Kvalita sloupce	Podíl hodnot ve sloupci, který je platný, obsahuje chyby nebo je prázdný.
Distribuce sloupců	Frekvence a distribuce hodnot v každém sloupci.
Profil sloupce	Podrobné statistiky o vybraném sloupci

S těmito funkcemi můžete také pracovat, což vám pomůže připravit data.

Poznámka:

Další informace o nástrojích pro profilaci dat najdete v nástrojích pro profilaci dat.

Zdokumentujte svou práci

Zdokumentování Power Query řešení zadáním kroků, dotazů a skupin smysluplných názvů a popisů Tyto podrobnosti usnadňují pochopení a údržbu každé transformace.

I když Power Query automaticky vytvoří název kroku v podokně použitých kroků, můžete také přejmenovat kroky nebo přidat popis některého z nich.

Poznámka:

Další informace o všech dostupných funkcích a komponentách, které najdete v podokně použitých kroků, najdete v seznamu Použitý postup.

Rozdělení velkých dotazů na moduly

Rozdělte velký Power Query dotaz na menší odkazované dotazy, abyste usnadnili pochopení a údržbu fází transformace. I když jeden dotaz může obsahovat všechny transformace a výpočty, které potřebujete, je jednodušší dotaz s mnoha kroky spravovat, když jeden dotaz odkazuje na další.

Například má následující dotaz devět kroků a obsahuje krok Sloučit s tabulkou Ceny.

V kroku sloučení s tabulkou Ceny můžete tento dotaz rozdělit na dva. Díky tomu je jednodušší pochopit kroky použité u prodejního dotazu před sloučením. Tuto operaci provedete kliknutím pravým tlačítkem na krok Sloučit s cenami a vybráním možnosti Extrahovat předchozí.

Zobrazí se výzva k zadání názvu nového dotazu pomocí dialogového okna. Tento krok efektivně rozdělí dotaz na dva dotazy. Jeden dotaz obsahuje všechny kroky před sloučením. Druhý dotaz obsahuje počáteční krok, který odkazuje na nový dotaz, a zbývající kroky, které jste měli v původním dotazu z tabulky Sloučit s cenami směrem dolů.

Můžete také použít odkazování na dotazy podle potřeby. Je však dobré zachovat dotazy na úrovni, která na první pohled nevypadá příliš složitě.

Poznámka:

Další informace o odkazování na dotazy najdete v podokně Vysvětlení dotazů.

Uspořádání dotazů do skupin

Pomocí skupin v podokně dotazy udržujte svou práci uspořádanou.

Jediným účelem skupin je zajistit uspořádání práce tím, že slouží jako složky pro vaše dotazy. Skupiny můžete vytvářet v rámci jiných skupin, pokud to někdy budete potřebovat. Přesouvání dotazů mezi skupinami je stejně snadné jako přetažení.

Snažte se dát svým skupinám smysluplný název, který dává smysl pro vás a váš případ.

Poznámka:

Další informace o všech dostupných funkcích a součástech nalezených v podokně dotazů najdete v podokně Vysvětlení dotazů.

Dotazy připravené na budoucnost

Návrh dotazů pro zpracování očekávaných změn ve zdrojových datech, aby budoucí aktualizace pokračovaly úspěšně. Power Query poskytuje transformace, které vytvoří dotaz odolný, když se změní řádky, sloupce nebo hodnoty ve zdroji dat.

Definujte rozsah dotazu, včetně toho, co by měl dělat a co by měl zohlednit z hlediska struktury, rozložení, názvů sloupců, datových typů a všech dalších relevantních součástí.

Následující transformace můžou pomoct dotazu zůstat odolný vůči změnám:

Scénář zdrojových dat	Transformace Power Query	Další informace
Počet řádků dat se změní, ale musíte odebrat pevný počet řádků zápatí.	Odebrání dolních řádků	Filtrování tabulky podle pozice řádku
Počet sloupců se změní, ale dotaz potřebuje jenom konkrétní sloupce.	Výběr sloupců	Výběr nebo odebrání sloupců
Počet sloupců se změní, ale dotaz musí převést jenom konkrétní podmnožinu.	Otočit pouze vybrané sloupce	Převést sloupce na řádky
Převod datového typu způsobí chyby pro hodnoty, které neodpovídají cílovému typu.	Odeberte řádky obsahující chyby.	Řešení chyb

Použití parametrů

Pomocí Power Query parametrů můžete ukládat a spravovat hodnoty, které můžete opakovaně používat v transformacích, funkcích zdroje dat a vlastních funkcích. Parametry usnadňují aktualizaci dotazů, protože místo úprav jednotlivých dotazů, které ho používají, můžete změnit hodnotu v jednom umístění. Mezi běžné scénáře patří:

• Argument kroku: Jako argument více transformací řízených z uživatelského rozhraní použijte parametr.

  

• Argument vlastní funkce: Vytvoření nové funkce z dotazu a odkazování na parametry jako argumenty vlastní funkce.

  

Hlavními výhodami vytváření a používání parametrů jsou:

• Centralizované zobrazení všech parametrů prostřednictvím okna Spravovat parametry

  

• Opakovaně použitelný parametr v několika krocích nebo dotazech.

• Vytváření vlastních funkcí je jednoduché a snadné.

Parametry můžete dokonce použít v některých argumentech datových konektorů. Při připojování k databázi SQL Serveru můžete například vytvořit parametr pro název serveru. Tento parametr pak můžete použít v dialogovém okně databáze SQL Serveru.

Pokud změníte umístění serveru, stačí aktualizovat parametr názvu serveru a vaše dotazy se aktualizují.

Poznámka:

Další informace o vytváření a používání parametrů najdete v tématu Použití parametrů.

Vytváření opakovaně použitelných funkcí

Pokud potřebujete použít stejnou sadu transformací na různé dotazy nebo hodnoty, vytvořte Power Query vlastní funkci. Power Query vlastní funkce mapuje sadu vstupních hodnot na jednu výstupní hodnotu a vytvoří se z nativních funkcí a operátorů jazyka vzorců Power Query M.

Řekněme například, že máte více dotazů nebo hodnot, které vyžadují stejnou sadu transformací. Můžete vytvořit vlastní funkci, kterou později vyvoláte vůči dotazům nebo hodnotám podle vašeho výběru. Tato vlastní funkce šetří čas a pomáhá spravovat sadu transformací v centrálním umístění, které můžete kdykoli změnit.

Vlastní funkce Power Query je možné vytvářet z existujících dotazů a parametrů. Představte si například dotaz, který má několik kódů jako textový řetězec a chcete vytvořit funkci, která tyto hodnoty dekóduje.

Začnete tím, že budete mít parametr s hodnotou, která slouží jako příklad.

Z daného parametru vytvoříte nový dotaz, ve kterém použijete transformace, které potřebujete. V tomto případě chcete rozdělit kód PTY-CM1090-LAX do několika komponent:

• Původ = PTY
• Destinace = LAX
• Airline = CM
• FlightID = 1090

Potom můžete tento dotaz transformovat na funkci tak, že kliknete pravým tlačítkem myši na dotaz a vyberete Vytvořit funkci. Nakonec můžete vlastní funkci vyvolat do libovolného z dotazů nebo hodnot.

Po několika dalších transformacích uvidíte, že jste dosáhli požadovaného výstupu a použili logiku pro takovou transformaci z vlastní funkce.

Poznámka:

Další informace o vytváření a používání vlastních funkcí v Power Query najdete v tématu Vlastní funkce.