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ší prostředí 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 mnohem 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, jak je znázorněno na následujícím obrázku.

Poznámka:

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

Filtrování brzy

Vždy se doporučuje filtrovat data v počátečních fázích dotazu nebo co nejdříve. Některé konektory budou využívat vaše 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 tomu se budete moct lépe soustředit na úkol tím, že zobrazíte jenom data, která jsou relevantní v části náhledu 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 příkladu pro sloupec data, data a času nebo dokonce data časového pásma.

Tyto filtry specifické pro typ vám můžou pomoct vytvořit dynamický filtr, který vždy načte data, která jsou v předchozím x počtu sekund, minutách, hodinách, dnech, týdnech, měsících, čtvrtletích nebo letech, jak je znázorněno na následujícím obrázku.

Poznámka:

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

Poslední operace provádění drahých operací

Některé operace vyžadují čtení celého zdroje dat, aby se vrátily všechny výsledky, a proto budou pomalé zobrazit náhled v Editor Power Query. 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 "streams" by a výsledky se vrátí po cestě. V Editor Power Query je potřeba, aby tyto operace četly dostatek zdrojových dat, aby se naplnila verze Preview.

Pokud je to možné, nejprve proveďte takové operace streamování a proveďte ještě dražší operace. To vám pomůže 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 Editor Power Query pomalé, zvažte nejprve operaci Zachovat první řádky a omezte počet řádků, na kterých 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 budou dostupné možnosti ve skupině sloupců Datum a čas v nabídce Přidat sloupec . Pokud ale sloupec nemá nastavený datový typ, budou tyto možnosti zašedlé.

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

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 o tom, jak vám může pomoct v datových typech.

Poznámka:

Další informace o důležitosti datových typů a o tom, jak s nimi pracovat, najdete v tématu 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, které znázorňují, 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é možná budete 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.

Tento dotaz můžete rozdělit na dva v kroku sloučení s tabulkou Ceny. Díky tomu je jednodušší pochopit kroky použité u prodejního dotazu před sloučením. Tuto operaci provedete tak, že kliknete pravým tlačítkem na krok Sloučit s cenami a vyberete možnost Extrahovat předchozí .

Zobrazí se výzva k zadání názvu nového dotazu pomocí dialogového okna. Tím se dotaz efektivně rozdělí na dva dotazy. Jeden dotaz bude obsahovat všechny dotazy před sloučením. Druhý dotaz bude mít počáteční krok, který bude odkazovat na váš 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 použití odkazů na dotazy podle potřeby. Je ale vhodné zachovat dotazy na úrovni, která na první pohled nezpůsobí příliš mnoho kroků.

Poznámka:

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

Vytvoření skupin

Skvělý způsob, jak zajistit uspořádání práce, je využ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 skupin, které byste někdy potřebovali. 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ý během budoucí aktualizace nebude mít žádné problémy, 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 převést jenom podmnožinu sloupců, můžete použít funkci převést jenom na 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 – Parametr můžete použít jako argument více transformací řízených z uživatelského rozhraní.

  

• 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í

Pokud zjistíte, že jste v situaci, kdy potřebujete použít stejnou sadu transformací na různé dotazy nebo hodnoty, vytvořte vlastní funkci Power Query, která se dá opakovaně použít tolikrát, kolikrát potřebujete, může být přínosná. 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, který má hodnotu, 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 na několik komponent:

• Origin = PTY
• Cíl = 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, jak je znázorněno na následujícím obrázku.

Po několika dalších transformacích uvidíte, že jste dosáhli požadovaného výstupu a využ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.