Osvedčené postupy pri práci s Power Query

Tento článok obsahuje niekoľko tipov a trikov, ako čo najlepšie vyžiť zo svojej ponuky údajov v Power Query.

Výber pravého konektora

Power Query ponúka obrovský počet konektorov údajov. Tieto konektory majú rozsah od zdrojov údajov, napríklad TXT, CSV Excel a Excel, až po databázy ako Microsoft SQL Server a obľúbené služby SaaS, ako napríklad Microsoft Dynamics 365 a Salesforce. Ak sa váš zdroj údajov v okne Získať údaje nenachádza, vždy môžete použiť konektor ODBC alebo OLEDB na pripojenie k zdroju údajov.

Pomocou najlepšieho konektora pre túto úlohu budete mať k dispozícii najlepšie prostredie a výkon. Napríklad pri pripájaní k databáze SQL Server vám konektor SQL Server namiesto konektora ODBC poskytuje nielen omnoho lepšie funkcie získať údaje, ale konektor SQL Server tiež ponúka funkcie, ktoré vám môžu zlepšiť vaše skúsenosti a výkon, ako napríklad skladanie dotazov. Ďalšie informácie o skladanie dotazov nájdete v téme Power Query skladanie dotazov.

Každý údajový konektor má štandardné prostredie, ako je vysvetlené v časti Získavanie údajov. Toto štandardizované prostredie má fázu s názvom Ukážka údajov. V tejto fáze bude k dispozícii prehľadné okno na výber údajov, ktoré chcete získať zo zdroja údajov, ak to konektor povoľuje, a jednoduchú ukážku údajov pre tieto údaje. V okne Navigátor môžete dokonca vybrať viacero množiny údajov zo zdroja údajov, ako je to znázornené na nasledujúcom obrázku.

Okno navigátora ukážky.

Poznámka

Ak chcete zobraziť úplný zoznam dostupných konektorov v téme Power Query, pozrite si časť Konektory v Power Query.

Filtrovanie čo najskôr

Vždy sa odporúča filtrovať údaje v počiatočných fázach dotazu alebo čo najskôr. Niektoré konektory budú využívať vaše filtre prostredníctvom skladanie dotazov, ako je popísané v Power Query skladanie dotazov. Osvedčil sa tiež odfiltrovať všetky údaje, ktoré vo vašom prípade nie sú relevantné. Umožní vám to lepšie sa sústrediť na túto úlohu tým, že sa budú zobrazovať iba údaje, ktoré sú relevantné v sekcii ukážky údajov.

Pomocou ponuky automatického filtrovania, v ktorej sa zobrazuje odlišný zoznam hodnôt našlo v stĺpci, môžete vybrať hodnoty, ktoré chcete zachovať alebo odfiltrovať. Pomocou panela hľadania môžete tiež vyhľadať hodnoty v stĺpci.

Ponuka Automatický filter v Power Query.

Môžete tiež využiť filtre špecifické pre jednotlivé typy, ako napríklad V predchádzajúcej verzii pre stĺpec dátum, dátum a čas alebo dokonca dátumové časové pásmo.

typ konkrétneho filtra pre stĺpec dátumov.

Tieto filtre špecifické pre typ vám môžu pomôcť vytvoriť dynamický filter, ktorý vždy načíta údaje, ktoré sú uvedené v predchádzajúcom x počte sekúnd, minút, hodín, dní, týždňov, mesiacov, štvrťrokov alebo rokov, ako je to predstavené na nasledujúcom obrázku.

Nachádza sa v predchádzajúcom filtri špecifickom pre dátum.

Poznámka

Ďalšie informácie o filtrovaní údajov na základe hodnôt zo stĺpca nájdete v téme Filtrovanie podľa hodnôt.

Použitie správnych typov údajov

Niektoré funkcie Power Query sú kontextové vzhľadom na typ údajov vybratého stĺpca. Keď napríklad vyberiete stĺpec dátumov, budú k dispozícii dostupné možnosti v skupine Stĺpec dátumu a času v ponuke Pridať stĺpec. Ak však stĺpec nemá množinu typu údajov, tieto možnosti budú sivé.

Zadajte konkrétnu možnosť v ponuke Pridať stĺpec.

Podobná situácia nastane aj pre filtre špecifické pre konkrétny typ, pretože sú špecifické pre určité typy údajov. Ak váš stĺpec nemá definovaný správny typ údajov, tieto filtre špecifické pre konkrétny typ nebudú k dispozícii.

typ konkrétneho filtra pre stĺpec dátumov.

Je dôležité, aby ste vždy pracujú so s správnymi typmi údajov pre stĺpce. Pri práci so štruktúrovanými zdrojmi údajov, ako sú napríklad databázy, sa informácie o type údajov preniesli zo schémy tabuľky v databáze. V prípade neštruktúrových zdrojov údajov, ako sú napríklad txt alebo súbory CSV, je dôležité, aby ste nastavili správne typy údajov pre stĺpce prichádzajúce z tohto zdroja údajov. V predvolenom nastavení Power Query automatické zisťovanie typu údajov pre neštruktúrované zdroje údajov. Viac informácií o tejto funkcii a o tom, ako vám môže pomôcť pri typoch údajov.

Poznámka

Ďalšie informácie o dôležitosti typov údajov a o tom, ako s nimi pracovať, nájdete v téme Typy údajov.

Preskúmanie údajov

Skôr než začnete pripraviť údaje a pridávať nové kroky transformácie, odporúčame vám povoliť nástroje na profilovanie údajov Power Query aby ste mohli jednoducho zistiť informácie o vašich údajoch.

Nástroje na ukážku údajov alebo profilovanie údajov v Power Query.

Tieto nástroje na profilovanie údajov vám pomôžu lepšie porozumieť vašim údajom. Nástroje poskytujú malé vizualizácie, ktoré zobrazujú informácie pre každý stĺpec, ako napríklad:

  • Kvalita stĺpcov — Poskytuje malý pruhový graf a tri indikátory s znázornenie toho, koľko hodnôt v stĺpci spadajú do kategórií platných, chybných alebo prázdnych hodnôt.
  • Distribúcia stĺpcov — Poskytuje množinu vizuálov pod názvami stĺpcov, ktoré prezentú frekvenciu a distribúciu hodnôt v každom zo stĺpcov.
  • Profil stĺpca — Poskytuje dôkladnejšie zobrazenie stĺpca a súvisiacich štatistík.

S týmito funkciami môžete pracovať aj a pripraviť údaje.

Možnosti na podržte ukazovateľ myši na možnosti v časti Kvalita údajov.

Poznámka

Ďalšie informácie o nástrojoch na profilovanie údajov nájdete v téme Nástroje profilovania údajov.

Dokumentujte svoju prácu

Dotazy odporúčame dokumentovať premenovaním alebo pridaním popisu do svojich krokov, dotazov alebo skupín podľa vašich predsudkov.

Zatiaľ Power Query ktorá automaticky vytvorí názov kroku na table Použité kroky, kroky môžete tiež premenovať alebo do ľubovoľného z nich pridať popis.

Tabla Použité kroky s pridanými zdokumentovami krokmi a popisom.

Poznámka

Ďalšie informácie o všetkých dostupných funkciách a súčastiach, ktoré sa nachádzajú na table použité kroky, nájdete v téme Používanie zoznamu Použité kroky.

Modulový prístup

Úplne je možné vytvoriť jeden dotaz, ktorý bude obsahovať všetky potrebné transformácie a výpočty. Ak však dotaz obsahuje veľký počet krokov, možno by bolo vhodné rozdeliť dotaz do viacerých dotazov, na ktoré jeden dotaz odkazuje. Cieľom tohto prístupu je zjednodušiť a dekódovať fázy transformácie na menšie časti, aby boli jednoduchšie pochopiteľné.

Povedzme napríklad, že máte dotaz s deviatimi krokmi zobrazenými na nasledujúcom obrázku.

Tabla Použité kroky s pridanými zdokumentovami krokmi a popisom.

Tento dotaz môžete rozdeliť na dva v kroku tabuľky Zlúčiť s cenami. Takto bude jednoduchšie pochopiť kroky, ktoré boli použité na dotazy predaja pred zlúčením. Ak chcete vykonať túto operáciu, kliknite pravým tlačidlom myši na krok tabuľky Zlúčiť s cenami a vyberte možnosť Extrahovať predchádzajúce.

Extrahovať predchádzajúci krok.

Potom sa zobrazí výzva s dialógovým oknom, aby ste nový dotaz pomenovali. Týmto sa dotaz efektívne rozdelí na dva dotazy. Jeden dotaz bude mať všetky dotazy pred zlúčením. Druhý dotaz bude mať počiatočný krok, ktorý bude odkazovať na nový dotaz a zvyšné kroky, ktoré ste mali v pôvodnom dotaze, z kroku zlúčiť s cenami nadol.

Pôvodný dotaz po akcii extrahovanie predchádzajúceho kroku.

Môžete tiež využiť odkazovanie na dotazy podľa toho, ako to vyhovuje. Je však vhodné udržiavať dotazy na úrovni, ktorá sa na prvý pohľad nezrozumie, pretože v mnohých krokoch sa to ešte viac nebude zdať skľučujúca.

Poznámka

Ďalšie informácie o odkazoch na dotazy nájdete v časti Vysvetlenie tably dotazov.

Tvorba skupín

Výborný spôsob usporiadania práce je využitie skupín na table dotazov.

Práca so skupinami v Power Query.

Výhradným účelom skupín je pomôcť vám usporiadať vašu prácu tak, že slúži ako priečinky pre vaše dotazy. Môžete vytvárať skupiny v rámci skupín v prípade, že to niekedy budete potrebovať. Presúvanie dotazov v rámci skupín je rovnako jednoduché ako presun myšou.

Skúste dať skupinám zmysluplný názov, ktorý pre vás aj váš prípad má zmysel.

Poznámka

Ďalšie informácie o všetkých dostupných funkciách a súčastiach, ktoré sa nachádzajú na table dotazov, nájdete v časti Vysvetlenie tably dotazov.

Dotazy s future-proofingom

Uistite sa, že je pre vás najvyššou prioritou vytvorenie dotazu, ktorý nebude mať žiadne problémy počas budúceho obnovenia. V tejto oblasti existuje Power Query funkcií, ktoré sú vhodné na to, aby boli vaše dotazy pružné, a obnovovať sa aj pri zmene niektorých komponentov zdroja údajov.

Rozsah dotazu sa odporúča definovať, čo má dotaz robiť a čo by mal brať do úvahy z hľadiska štruktúry, rozloženia, názvov stĺpcov, typov údajov a všetkých ostatných súčastí, ktoré považujete za relevantné pre rozsah.

Príkladmi transformácií, ktoré vám môžu pomôcť v zmieriť vaše dotazy v prípade zmien, sú:

  • Ak má váš dotaz dynamický počet riadkov s údajmi, ale pevný počet riadkov, ktoré slúžia ako päta, ktorá sa má odstrániť, môžete použiť funkciu Odstrániť spodné riadky.

    Poznámka

    Ďalšie informácie o filtrovaní údajov podľa pozície riadka nájdete v téme Filtrovanie tabuľky podľa pozície riadka.

  • Ak má váš dotaz dynamický počet stĺpcov, ale v množine údajov potrebujete vybrať len konkrétne stĺpce, môžete použiť funkciu Vybrať stĺpce.

    Poznámka

    Ďalšie informácie o výbere alebo odstránení stĺpcov nájdete v téme Výber alebo odstránenie stĺpcov.

  • Ak má dotaz dynamický počet stĺpcov a je potrebné zrušiť kontingenčnosť iba podmnožiny stĺpcov, môžete použiť funkciu Zrušiť kontingenčnosť len vybratých stĺpcov.

    Poznámka

    Ďalšie informácie o možnostiach na zrušenie kontingenčnosti stĺpcov nájdete v téme Zrušenie kontingenčnosti stĺpcov.

  • Ak má dotaz krok, ktorý mení typ údajov stĺpca, ale niektoré bunky vrátia chyby, keďže hodnoty nezodpovedajú požadovanému typu údajov, môžete odstrániť riadky, pri ktorých sa vyskytli chybové hodnoty.

    Poznámka

    Ďalšie informácie o práci a riešení chýb nájdete v téme Spracovanie chýb.

Použitie parametrov

Najlepším postupom je vytvorenie dynamických a flexibilných dotazov. Parametre v Power Query vám pomôžu vytvoriť dynamickejšie a flexibilnejšie dotazy. Parameter slúži ako spôsob jednoduchého ukladania a spravovania hodnoty, ktorá sa dá opakovane používať mnohými rôznymi spôsobmi. Častejšie sa však používa v dvoch scenároch:

  • Argument kroku — Parameter môžete použiť ako argument viacerých transformácií riadených z používateľského rozhrania.

    Vyberte parameter pre argument transformácie.

  • Argument vlastnej funkcie — Novú funkciu môžete vytvoriť z dotazu a odkazovať na parametre ako argumenty vlastnej funkcie.

    Vytvoriť funkciu.

Hlavné výhody vytvárania a používania parametrov sú:

  • Centralizované zobrazenie všetkých parametrov cez okno Spravovať parametre.

    Okno Spravovať parametre.

  • Opätovné použiteľnosť parametra vo viacerých krokoch alebo dotazoch.

  • Umožňuje jednoduché a jednoduché vytváranie vlastných funkcií.

Parametre môžete použiť aj v niektorých argumentoch konektorov údajov. Pri pripájaní k vašej novej databáze môžete napríklad vytvoriť parameter pre SQL Server servera. Potom by ste tento parameter mohli použiť v dialógovom okne SQL Server databáze.

SQL Server dialógového okna s parametrom pre názov servera.

Ak zmeníte umiestnenie servera, všetko, čo potrebujete urobiť, je aktualizovať parameter názvu servera a dotazy sa aktualizujú.

Poznámka

Ďalšie informácie o vytváraní a používaní parametrov nájdete v téme Používanie parametrov.

Vytváranie opätovne použiteľných funkcií

Ak zistíte, že potrebujete použiť rovnakú množinu transformácií na rôzne dotazy alebo hodnoty, vytvorenie vlastnej funkcie Power Query, ktorú možno opakovane použiť, koľkokrát potrebujete, môže byť prínosné. Vlastná Power Query je mapovanie z množiny vstupných hodnôt na jednu výstupnú hodnotu a je vytvorená z natívnych funkcií a operátorov M.

Povedzme napríklad, že máte viacero dotazov alebo hodnôt, ktoré vyžadujú rovnakú množinu transformácií. Môžete vytvoriť vlastnú funkciu, ktorú neskôr možno vyvolať vo všetkých dotazoch alebo hodnotách podľa vlastného výberu. Táto vlastná funkcia vám ušetrí čas a pomôže vám pri spravovaní množiny transformácií na centrálnom mieste, ktoré môžete kedykoľvek upraviť.

Power Query je možné vytvoriť z existujúcich dotazov a parametrov. Predstavte si napríklad dotaz, ktorý má niekoľko kódov ako textový reťazec a chcete vytvoriť funkciu, ktorá tieto hodnoty odkóduje.

Zoznam kódov.

Začnite tým, že budete mať parameter s hodnotou, ktorá slúži ako príklad.

Hodnota kódu vzorového parametra.

Z tohto parametra vytvoríte nový dotaz, v ktorom použijete potrebné transformácie. V tomto prípade chcete kód PTY-CM1090-LAX rozdeliť na viaceré súčasti:

  • Origin = PTY
  • Cieľ = LAX
  • Lína = CM
  • FlightID = 1090

Vzorový dotaz transformácie.

Tento dotaz môžete potom transformovať na funkciu kliknutím pravým tlačidlom myši na dotaz a výberom položky Vytvoriť funkciu. Nakoniec môžete vyvolať vlastnú funkciu do ľubovoľného z dotazov alebo hodnôt, ako je to znázornené na nasledujúcom obrázku.

Vyvolanie vlastnej funkcie.

Po niekoľkých ďalších transformáciách môžete vidieť, že ste dosiahli požadovaný výstup a využívali logiku pre takúto transformáciu z vlastnej funkcie.

Koncový výstupný dotaz po vyvolaní vlastnej funkcie.

Poznámka

Ďalšie informácie o vytváraní a používaní vlastných funkcií v balíku Power Query článku Vlastné funkcie.