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

Tento článek obsahuje několik tipů a triků, které vám pomohou maximálně využít možnosti transformace dat v Power Query.

Volba správného konektoru

Power Query nabízí velký počet datových konektorů. Tyto konektory se liší od zdrojů dat, jako jsou TXT, CSV a excelové soubory, až po databáze, jako je Microsoft SQL Server, a oblíbené služby SaaS, jako jsou Microsoft Dynamics 365 a Salesforce. Pokud se váš zdroj dat nezobrazuje v okně Získat data , můžete se ke zdroji dat připojit vždy pomocí konektoru ODBC nebo OLEDB.

Použití nejlepšího konektoru pro úlohu vám poskytne nejlepší uživatelský dojem a výkon. Například použití konektoru SQL Serveru místo konektoru ODBC při připojování k databázi SQL Serveru poskytuje nejen lepší možnosti získání dat , ale konektor SQL Serveru také nabízí funkce, které můžou zlepšit vaše prostředí a výkon, například posouvání dotazů. Další informace o posouvání dotazů 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.

Filtrovat co nejdříve

Vždy doporučujeme data filtrovat v počátečních fázích dotazu nebo co nejdříve. Některé konektory využívají filtry prostřednictvím posouvání dotazů, jak je popsáno v tématu Přehled vyhodnocení dotazů a posouvání dotazů v Power Query. Osvědčeným postupem je také vyfiltrovat všechna data, která nejsou pro váš případ relevantní. Díky tomuto filtrování se lépe zaměříte na svoji úlohu, a to jenom zobrazením dat, která jsou relevantní v části Náhled dat.

Pomocí nabídky automatického filtrování, která zobrazuje jedinečný seznam hodnot nalezených ve sloupci, můžete vybrat hodnoty, které chcete zachovat nebo odfiltrovat. Pomocí panelu hledání můžete také 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.

Screenshot specifického filtru ukázkového typu pro sloupec datum se zvýrazněnou předchozí možností.

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ějí drahé operace dlouho?

Některé operace vyžadují čtení celého zdroje dat, aby se vrátily všechny výsledky, a proto je v editoru Power Query pomalé zobrazení náhledu. Pokud například provedete řazení, je možné, že prvních několik seřazených řádků je na konci zdrojových dat. Aby se tedy vrátily 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.

Dočasně pracovat s podmnožinou dat

Pokud je přidání nových kroků do dotazu v editoru Power Query pomalé, zvažte nejprve operaci Zachovat první řádky a omezte počet řádků, se kterou pracujete. Po přidání všech potřebných kroků odeberte krok Zachovat první řádky.

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

Některé funkce v Power Query jsou kontextové pro datový typ vybraného sloupce. Například při výběru sloupce kalendářního data jsou dostupné možnosti ve skupině sloupců Datum a čas v nabídce Přidat sloupec použitelné. Pokud ale 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.

Prozkoumání dat

Než začnete připravovat data a přidávat nové kroky transformace, doporučujeme povolit nástrojům pro profilaci dat Power Query snadné zjišťování informací o vašich datech.

Tyto nástroje pro profilaci dat vám pomůžou lépe porozumět vašim datům. Nástroje poskytují malé vizualizace, které zobrazují informace na základě jednotlivých sloupců, například:

• Kvalita sloupce – poskytuje malý pruhový graf a tři indikátory s reprezentací, kolik hodnot ve sloupci spadá do kategorií platných, chybových nebo prázdných hodnot.
• Distribuce sloupců – poskytuje sadu vizuálů pod názvy sloupců, které ukazují frekvenci a distribuci hodnot v jednotlivých sloupcích.
• Profil sloupce – poskytuje podrobnější zobrazení sloupce a statistiky, které jsou k němu přidružené.

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

Doporučujeme zdokumentovat dotazy přejmenováním nebo přidáním popisu do kroků, dotazů nebo skupin podle potřeby.

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.

Využití modulárního přístupu

Je zcela možné vytvořit jeden dotaz, který obsahuje všechny transformace a výpočty, které byste mohli potřebovat. Pokud ale dotaz obsahuje velký počet kroků, může být vhodné dotaz rozdělit na více dotazů, kde jeden dotaz odkazuje na další. Cílem tohoto přístupu je zjednodušit a oddělit fáze transformace na menší části, aby byly srozumitelnější.

Řekněme například, že máte dotaz s devíti kroky zobrazenými na následujícím obrázku.

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 dotazy 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é využí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ů.

Vytváření skupin

Skvělý způsob, jak udržet vaši práci uspořádanou, je použití skupin v podokně dotazů.

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 kontroly pravopisu pro budoucnost

Ujistěte se, že vytvoříte dotaz, který nebude mít žádné problémy během budoucí aktualizace. To je nejvyšší prioritou. V Power Query je několik funkcí, které umožňují, aby byl dotaz odolný vůči změnám a mohl se aktualizovat i v případě, že se některé součásti vašeho zdroje dat změní.

Osvědčeným postupem je definovat rozsah dotazu, co má dělat, a co by měl zohlednit z hlediska struktury, rozložení, názvů sloupců, datových typů a jakékoli další komponenty, které považujete za relevantní pro daný obor.

Mezi příklady transformací, které vám můžou pomoct zajistit odolnost dotazu vůči změnám, patří:

• Pokud má dotaz dynamický počet řádků s daty, ale pevný počet řádků, které slouží jako zápatí, které by se měly odebrat, můžete použít funkci Odebrat dolní řádky .

  Poznámka:

  Další informace o filtrování dat podle pozice řádku najdete v části Filtrování tabulky podle pozice řádku.

• Pokud má dotaz dynamický počet sloupců, ale potřebujete vybrat jenom konkrétní sloupce ze sady dat, můžete použít funkci Zvolit sloupce .

  Poznámka:

  Další informace o výběru nebo odebrání sloupců najdete v části Volba nebo odebrání sloupců.

• Pokud má váš dotaz dynamický počet sloupců a potřebujete rozbalit jenom podmnožinu sloupců, můžete použít funkci rozbalit pouze vybrané sloupce.

  Poznámka:

  Další informace o možnostech převést sloupce na řádky najdete v části Převést sloupce na řádky.

• Pokud dotaz obsahuje krok, který změní datový typ sloupce, ale některé buňky můžou přinést chyby, protože hodnoty neodpovídají požadovanému datovému typu, můžete odebrat řádky, které přinesly chybové hodnoty.

  Poznámka:

  Další informace o práci a práci s chybami najdete v tématu Práce s chybami.

Použití parametrů

Osvědčeným postupem je vytváření dotazů, které jsou dynamické a flexibilní. Parametry v Power Query pomáhají vytvářet dotazy dynamičtější a flexibilnější. Parametr slouží jako způsob, jak snadno ukládat a spravovat hodnotu, kterou lze opakovaně používat mnoha různými způsoby. Častěji se ale používá ve dvou scénářích:

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

  

• Argument vlastní funkce: Můžete vytvořit novou funkci z dotazu a odkazovat 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í

Můžete se ocitnout v situaci, kdy potřebujete použít stejnou sadu transformací na různé dotazy nebo hodnoty. V tomto případě můžete vytvořit vlastní funkci Power Query, která se dá opakovaně použít tolikrát, kolikrát potřebujete. Vlastní funkce Power Query je mapování ze sady vstupních hodnot na jednu výstupní hodnotu a vytváří se z nativních funkcí a operátorů jazyka 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 můžete vyvolat vůči dotazům nebo hodnotám podle vašeho výběru. Tato vlastní funkce vám ušetří čas a pomůže vám při správě sady 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 článku Vlastní funkce.