Najvhodnejšie postupy pri práci s power query

Tento článok obsahuje niekoľko tipov a trikov, ktoré vám pomôžu vyžiť z vašich údajových problémov v doplnku Power Query.

Výber správneho konektora

Power Query ponúka obrovské množstvo konektorov údajov. Tieto konektory siahajú od zdrojov údajov, ako sú TXT, CSV a excelové súbory, až po databázy, ako je napríklad Microsoft SQL Server, a obľúbené služby SaaS, ako napríklad Microsoft Dynamics 365 a Salesforce. Ak sa váš zdroj údajov nezobrazuje v okne Získať údaje , na pripojenie k zdroju údajov môžete vždy použiť konektor ODBC alebo OLEDB.

Použitie najlepšieho konektora pre úlohu vám poskytne najlepšie prostredie a výkon. Napríklad použitie konektora SQL Servera namiesto konektora ODBC pri pripájaní k databáze SQL Servera poskytuje nielen oveľa lepšie možnosti na získanie údajov , ale konektor SQL Servera ponúka aj funkcie, ktoré môžu zlepšiť vaše možnosti a výkon, ako je napríklad postupné posúvanie dotazov. Ďalšie informácie o postupnom posúvaní dotazov nájdete v téme Prehľad vyhodnocovania dotazov a postupné posúvanie dotazov v doplnku Power Query.

Každý konektor údajov sa riadi štandardnou skúsenosťou, ako je vysvetlené v téme Získavanie údajov. Toto štandardizované prostredie má fázu s názvom Ukážka údajov. V tejto fáze sa pre vás poskytuje prehľadné okno na výber údajov, ktoré chcete získať zo zdroja údajov, ak to konektor povoľuje, a jednoduchú ukážku údajov. Zo zdroja údajov môžete dokonca vybrať viacero množín údajov prostredníctvom okna Navigátor , ako je to znázornené na nasledujúcom obrázku.

Poznámka

Ak si chcete pozrieť úplný zoznam dostupných konektorov v doplnku Power Query, prejdite do časti Pripojenie or v doplnku Power Query.

Filtrovať včas

Vždy sa odporúča filtrovať údaje v skorých štádiách dotazu alebo čo najskôr. Niektoré konektory budú využívať vaše filtre prostredníctvom postupného posúvania dotazov, ako je popísané v téme Prehľad vyhodnocovania dotazov a postupné posúvanie dotazov v doplnku Power Query. Najvhodnejším postupom je tiež odfiltrovať údaje, ktoré nie sú pre váš prípad relevantné. To vám umožní lepšie sa sústrediť na svoju úlohu tým, že sa zobrazia len údaje, ktoré sú relevantné v časti Ukážka údajov.

Môžete použiť ponuku automatického filtrovania, ktorá zobrazuje odlišný zoznam hodnôt vo vašom stĺpci a vybrať tak hodnoty, ktoré chcete ponechať alebo odfiltrovať. Na vyhľadanie hodnôt v stĺpci môžete použiť aj panel hľadania.

Môžete tiež využiť filtre špecifické pre konkrétny typ, ako napríklad V predchádzajúcom pre stĺpec dátumu, dátumu a času, alebo dokonca stĺpec časového pásma dátumu.

Tieto filtre špecifické pre daný typ vám môžu pomôcť vytvoriť dynamický filter, ktorý bude vždy načítavať údaje z predchádzajúcich x počtu sekúnd, minút, hodín, dní, týždňov, mesiacov, štvrťrokov alebo rokov, ako je znázornené na nasledujúcom obrázku.

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.

Do drahých operácií posledný

Niektoré operácie vyžadujú prečítanie úplného zdroja údajov, aby sa vrátili všetky výsledky, čím sa pri Editor Power Query bude pomaly zobrazovať ukážka. Ak napríklad vykonáte zoradenie, je možné, že prvých niekoľko zoradených riadkov sa nachádza na konci zdrojových údajov. Ak teda chcete vrátiť výsledky, operácia zoradenia musí najskôr prečítať všetky riadky.

Iné operácie (napríklad filtre) nemusia pred vrátením výsledkov čítať všetky údaje. Namiesto toho pracujú na údajoch spôsobom, ktorý sa nazýva "streaming". Údaje "streamujú" podľa a výsledky sa vrátia pozdĺž cesty. V Editor Power Query musia takéto operácie iba čítať dostatočný počet zdrojových údajov, aby sa vyplnila ukážka.

Ak je to možné, vykonajte najprv takéto operácie streamovania a vykonajte akékoľvek nákladnejšie operácie ako posledné. Pomôže to minimalizovať čas strávený čakaní na vykreslenie ukážky pri každom pridaní nového kroku do dotazu.

Dočasne pracovať s podmnožinou údajov

Ak je pridávanie nových krokov do dotazu v Editor Power Query pomalé, zvážte najprv operáciu Ponechať prvé riadky a obmedziť počet riadkov, s ktorými pracujete. Po pridaní všetkých potrebných krokov odstráňte krok Ponechať prvé riadky.

Použitie správnych typov údajov

Niektoré funkcie v doplnku Power Query sú kontextové pre typ údajov vybratého stĺpca. Napríklad pri výbere stĺpca dátumov budú k dispozícii možnosti v skupine stĺpcov Dátum a čas v ponuke Pridať stĺpec. Ak však stĺpec nemá množinu údajov, tieto možnosti budú sivé.

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

Je dôležité, aby ste vždy pracovali so správnymi typmi údajov pre stĺpce. Pri práci so štruktúrovanými zdrojmi údajov, ako sú napríklad databázy, informácie o type údajov sa prevezmú zo schémy tabuľky, ktorá sa nachádza v databáze. V prípade neštruktúrovaných zdrojov údajov, ako sú napríklad súbory TXT a CSV, je dôležité nastaviť správne typy údajov pre stĺpce pochádzajúce z tohto zdroja údajov. V predvolenom nastavení ponúka Power Query automatické zisťovanie typu údajov pre neštruktúrované zdroje údajov. Môžete si prečítať viac o tejto funkcii a o tom, ako vám môže pomôcť v 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 s prípravou údajov a pridaním nových krokov transformácie, odporúčame, aby ste umožnili nástrojom profilovania údajov Power Query jednoducho objaviť informácie o vašich údajoch.

Tieto nástroje na profilovanie údajov vám pomôžu lepšie pochopiť vaše údaje. Nástroje poskytujú malé vizualizácie, ktoré zobrazujú informácie podľa stĺpca, ako napríklad:

• Kvalita stĺpcov – poskytuje malý pruhový graf a tri indikátory s vyjadrením toho, koľko hodnôt v stĺpci patrí 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é prezentujú frekvenciu a distribúciu hodnôt v každom stĺpci.
• Profil stĺpca – poskytuje dôkladnejšie zobrazenie stĺpca a s ním súvisiace štatistiky.

Môžete tiež pracovať s týmito funkciami, ktoré vám pomôžu pripraviť údaje.

Poznámka

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

Dokumentácia práce

Odporúča sa zdokumentovať dotazy premenovaním alebo pridaním popisu k postupom, dotazom alebo skupinám podľa potreby.

Hoci power query na table Použité kroky automaticky vytvorí názov kroku, kroky môžete tiež premenovať alebo k nim pridať popis.

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 zozname Použité kroky.

Vytvorte modulárny prístup

Je úplne možné vytvoriť jeden dotaz, ktorý obsahuje všetky potrebné transformácie a výpočty. Ak však dotaz obsahuje veľké množstvo krokov, potom by bolo vhodné rozdeliť dotaz do viacerých dotazov, na ktoré jeden dotaz odkazuje ďalší. Cieľom tohto prístupu je zjednodušiť a rozdeľovať fázy transformácie na menšie kúsky tak, aby boli ľahšie pochopiteľné.

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

Tento dotaz by ste mohli rozdeliť na dve časti v kroku Tabuľky Zlúčiť s cenami. Týmto spôsobom je jednoduchšie pochopiť kroky, ktoré boli použité v dotaze na predaj 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 .

Potom sa zobrazí dialógové okno s výzvou na zadanie názvu nového dotazu. Tým sa dotaz efektívne rozdelí na dva dotazy. Jeden dotaz bude mať pred zlúčením všetky dotazy. Druhý dotaz bude mať počiatočný krok, ktorý odkazuje na váš nový dotaz, a zvyšné kroky, ktoré ste mali v pôvodnom dotaze, z kroku tabuľky Zlúčiť s cenami nadol.

Môžete tiež využiť podľa potreby využitie odkazovania na dotaz. Je však vhodné udržiavať dotazy na úrovni, ktorá sa pri toľkých krokoch nezdá byť na prvý pohľad skľučujúca.

Poznámka

Ďalšie informácie o odkazovaní dotazom nájdete v téme Pochopenie tably dotazov.

Vytváranie skupín

Skvelý spôsob, ako zachovať usporiadanie práce, je využitie skupín na table dotazov.

Jediným účelom skupín je pomôcť zachovať si prácu usporiadanú tak, že slúži ako priečinky pre vaše dotazy. Môžete vytvárať skupiny v rámci skupín, ak by ste niekedy potrebovali. Presúvanie dotazov v rámci skupín je rovnako jednoduché ako presunutie myšou.

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

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 téme Pochopenie tably dotazov.

Budúce dotazy na testovanie

Vytvorenie dotazu, ktorý nebude mať počas budúceho obnovenia žiadne problémy, je preto najvyššou prioritou. Power Query obsahuje niekoľko funkcií, vďaka ktorých je váš dotaz odolný voči zmenám a je možné ho obnoviť aj v prípadoch, keď sa zmenia niektoré súčasti zdroja údajov.

Najvhodnejším postupom je definovať rozsah dotazu podľa toho, čo má robiť a čo by mal zohľadniť, pokiaľ ide o štruktúru, rozloženie, názvy stĺpcov, typy údajov a všetky ostatné súčasti, ktoré sa považujú za relevantné pre rozsah.

Niekoľko príkladov transformácií, ktoré vám môžu pomôcť zabezpečiť odolnosť dotazu pred zmenami:

• Ak má 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á dotaz dynamický počet stĺpcov, ale potrebujete len vybrať konkrétne stĺpce z množiny údajov, 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 potrebujete zrušiť kontingenčnosť iba podmnožiny stĺpcov, môžete použiť funkciu zrušenia kontingenčnosti iba vybratých stĺpcov .

  Poznámka

  Ďalšie informácie o možnostiach zrušenia kontingennosti stĺpcov nájdete v časti Zrušenie kontingencnosti stĺpcov.

• Ak váš dotaz obsahuje krok, ktorý zmení typ údajov stĺpca, ale niektoré bunky vynesú chyby, pretože hodnoty nie sú v súlade s požadovaným typom údajov, môžete odstrániť riadky, ktoré priniesli chybové hodnoty.

  Poznámka

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

Použitie parametrov

Najlepším postupom je vytváranie dynamických a flexibilných dotazov. Parametre v doplnku Power Query pomáhajú vytvoriť dynamickejšie a flexibilnejšie dotazy. Parameter slúži ako spôsob, ako jednoducho ukladať a spravovať hodnotu, ktorú možno opätovne použiť 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.

  

• Argument Vlastná funkcia – môžete vytvoriť novú funkciu z dotazu a odkazovať na parametre ako argumenty vlastnej funkcie.

  

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

• Centralizované zobrazenie všetkých parametrov prostredníctvom okna Spravovať parametre .

  

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

• Vytváranie vlastných funkcií je jednoduché a jednoduché.

Parametre môžete použiť v niektorých argumentoch konektorov údajov. Pri pripájaní k databáze SQL Servera môžete napríklad vytvoriť parameter pre názov servera. Potom by ste mohli použiť tento parameter v dialógovom okne Databáza SQL Servera.

Ak zmeníte umiestnenie servera, stačí aktualizovať parameter vášho 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 sa nachádzate v situácii, že potrebujete použiť rovnakú množinu transformácií na rôzne dotazy alebo hodnoty, vytvorenie vlastnej funkcie Power Query, ktorú možno opätovne použiť toľkokrát, koľko potrebujete, môže byť prínosom. Vlastná funkcia 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 jazyka 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 môžete vyvolať na dotazy alebo hodnoty podľa vášho výberu. Táto vlastná funkcia by vám ušetrila čas a pomohla vám pri spravovaní množiny transformácií v centrálnom umiestnení, ktoré môžete kedykoľvek upraviť.

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

Začnete tým, že s parametrom, ktorý obsahuje hodnotu, ktorá slúži ako príklad.

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

• Origin = PTY
• Cieľ = LAX
• Letecká spoločnosť = CM
• FlightID = 1090

Potom môžete tento dotaz 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 ktoréhokoľvek z dotazov alebo hodnôt, ako je to znázornené na nasledujúcom obrázku.

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

Poznámka

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