Používanie vlastných funkcií

Ak sa ocitnete v situácii, keď potrebujete aplikovať rovnakú sadu transformácií na rôzne dotazy alebo hodnoty, vytvorenie vlastnej funkcie v Power Query, ktorú môžete použiť toľkokrát, koľko potrebujete, môže byť prospešné. 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.

Môžete si manuálne vytvoriť vlastnú funkciu Power Query pomocou jazyka vzorcov Power Query M, alebo používateľské rozhranie Power Query ponúka funkcie na zrýchlenie, zjednodušenie a zlepšenie procesu vytvárania a správy vlastnej funkcie.

Najprv prejdeme základné kroky na vytvorenie vlastnej funkcie s kódom v používateľskom rozhraní, potom sa zameriame na využitie rozhrania na premenu zložitých akcií na opakovane použiteľnú funkciu.

Dôležité

Tento článok popisuje, ako vytvoriť vlastnú funkciu v Power Query pomocou bežných transformácií dostupných v používateľskom rozhraní Power Query. Zameriava sa na základné koncepty vytvárania vlastných funkcií a odkazuje na ďalšie články v dokumentácii Power Query, kde nájdete viac informácií o konkrétnych transformáciách, na ktoré sa v tomto článku odkazuje.

Vytvorte vlastnú funkciu z kódu v používateľskom rozhraní

Poznámka

Nasledujúce kroky je možné vykonať v Power BI Desktop alebo pomocou Power Query zážitku, ktorý nájdete v Exceli pre Windows.

1. Použite skúsenosť s konektorom na pripojenie k dátam tam, kde sú uložené. Keď vyberiete svoje dáta, vyberte tlačidlo Transformovať dáta alebo Upraviť . Tým sa dostanete k zážitku s Power Query.
2. Kliknite pravým tlačidlom myši na prázdne miesto v paneli Dotazy vľavo.
3. Vyberte Prázdny dotaz.
4. V novom prázdnom okne dotazu vyberte menu Domov a potom Pokročilý editor.
5. Nahraďte šablónu svojou vlastnou funkciou. Napríklad:

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Vyberte položku Hotovo.

Pre viac informácií o vývoji vlastných funkcií pomocou jazyka formulí Power Query M si pozrite tento článok: Pochopenie Power Query M funkcií. V nasledujúcich sekciách nájdete návody, ktoré popisujú, ako môžete použiť používateľské rozhranie Power Query na vývoj vlastných funkcií bez písania kódu, a inštrukcie, ako môžete svoju vlastnú funkciu vyvolať vo svojom dotaze.

Vytvorte vlastnú funkciu z tutoriálu na referencie tabuľky

Poznámka

Nasledujúci príklad bol vytvorený pomocou desktopovej skúsenosti z Power BI Desktop a dá sa sledovať aj pomocou Power Query skúsenosti z Excelu pre Windows.

Môžete sa riadiť týmto príkladom stiahnutím ukážkových súborov použitých v tomto článku z nasledujúceho odkazu na stiahnutie. Pre jednoduchosť tento článok používa konektor Folder. Ak sa chcete dozvedieť viac o konektore priečinkov, choďte do priečinka. Cieľom tohto príkladu je vytvoriť vlastnú funkciu, ktorú je možné aplikovať na všetky súbory v danom priečinku predtým, než sa všetky dáta zo všetkých súborov spoja do jednej tabuľky.

Začnite tým, že pomocou zážitku Folder connector prejdete do priečinka, kde sa nachádzajú vaše súbory, a vyberiete Transformovať dáta alebo Upraviť. Tieto kroky vás zavedú k zážitku s Power Query. Kliknite pravým tlačidlom na binárnu hodnotu podľa vášho výberu v poli Obsah a vyberte možnosť Pridať ako nový dotaz . Pre tento príklad bol výber vykonaný pre prvý súbor zo zoznamu, ktorým je súbor 2019.csv. apríla .

Táto možnosť efektívne vytvára nový dotaz s navigačným krokom priamo do tohto súboru ako binárny súbor a názov tohto nového dotazu je cesta k vybranému súboru. Premenujte tento dotaz na Sample File.

Vytvorte nový parameter s názvom File Parameter a typom Binary. Použite dotaz Sample File ako predvolenú hodnotu a aktuálnu hodnotu.

Poznámka

Odporúčame vám prečítať si článok o parametroch , aby ste lepšie pochopili, ako vytvárať a spravovať parametre v Power Query.

Vlastné funkcie je možné vytvárať pomocou akéhokoľvek typu parametrov. Nie je potrebné, aby akákoľvek vlastná funkcia mala binárny parameter ako parameter.

Typ binárneho parametra sa zobrazuje iba v rozbaľovacom menu typu v dialógovom dialógu Parametre, keď máte dotaz, ktorý sa vyhodnocuje do binárneho systému.

Je možné vytvoriť vlastnú funkciu bez parametra. Toto sa bežne vyskytuje v situáciách, kde je možné vstup odvodiť z prostredia, kde sa funkcia volá. Napríklad funkcia, ktorá vezme aktuálny dátum a čas prostredia a vytvorí z týchto hodnôt špecifický textový reťazec.

Kliknite pravým tlačidlom na parameter súboru v paneli Dotazy . Vyberte možnosť Referencia .

Premenujte novo vytvorený dotaz z parametra súboru (2) na Transform Sample súbor.

Kliknite pravým tlačidlom na tento nový dotaz Transform Sample súboru a vyberte možnosť Vytvoriť funkciu .

Táto operácia efektívne vytvára novú funkciu, ktorá sa prepojí s dotazom Transform Sample súboru . Akékoľvek zmeny, ktoré urobíte v dotaze Transform Sample súboru , sa automaticky replikujú do vašej vlastnej funkcie. Počas vytvárania tejto novej funkcie použite súbor Transform ako názov funkcie.

Po vytvorení funkcie si všimnite, že pre vás je vytvorená nová skupina s názvom vašej funkcie. Táto nová skupina obsahuje:

• Všetky parametre, na ktoré sa odkazovalo vo vašom dotaze na Transform Sample súbor .
• Váš dotaz na Transform Sample súbor , bežne známy ako vzorový dotaz.
• Vaša novo vytvorená funkcia, v tomto prípade Transform súbor.

Aplikácia transformácií na ukážkový dotaz

Keď máte vytvorenú novú funkciu, vyberte dotaz s názvom Transform Sample súbor. Tento dotaz je teraz prepojený s funkciou Transform súboru , takže akékoľvek zmeny vykonané v tomto dotaze sa odrážajú vo funkcii. Toto prepojenie je známe ako koncept vzorového dotazu prepojeného s funkciou.

Prvá transformácia, ktorá musí prebehnúť v tomto dotaze, je taká, ktorá interpretuje binárny súbor. Môžete pravým kliknutím kliknúť na binárny súbor v náhľadovom paneli a vybrať možnosť CSV na interpretáciu binárky ako CSV súboru.

Formát všetkých CSV súborov v priečinku je rovnaký. Všetky majú hlavičku pokrývajúcu prvé štyri horné rady. Hlavičky stĺpcov sa nachádzajú v riadku 5 a údaje začínajú od riadku 6 smerom nadol, ako je znázornené na nasledujúcom obrázku.

Ďalšia sada transformačných krokov, ktoré je potrebné aplikovať na súbor Transform Sample , sú:

1. Odstráňte horné štyri riadky—Táto akcia odstráni riadky, ktoré sú považované za súčasť hlavičky súboru.

   

   Poznámka

   Ak sa chcete dozvedieť viac o tom, ako odstrániť riadky alebo filtrovať tabuľku podľa pozície riadku, choďte na Filtrovať podľa pozície riadku.

2. Povýšte hlavičky – Hlavičky vašej finálnej tabuľky sú teraz v prvom riadku tabuľky. Môžete ich propagovať, ako je znázornené na nasledujúcom obrázku.

   

Po propagácii hlavičiek stĺpcov Power Query automaticky automaticky pridá nový krok Zmenený typ , ktorý automaticky detekuje dátové typy pre každý stĺpec. Váš dotaz na súbor Transform Sample vyzerá ako ďalší obrázok.

Poznámka

Ak sa chcete dozvedieť viac o tom, ako povyšovať a degradovať nadpisy, choďte do sekcie Povýšiť alebo degradovať nadpisy stĺpcov.

Výstraha

Vaša funkcia Transform súboru závisí od krokov vykonaných v dotaze Transform Sample súboru . Ak sa však pokúsite manuálne upraviť kód funkcie Transform súboru , privíta vás upozornenie, ktoré znie The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Vyvolajte vlastnú funkciu ako nový stĺpec

Keď je vlastná funkcia teraz vytvorená a všetky transformačné kroky sú začlenené, môžete sa vrátiť k pôvodnému dotazu, kde máte zoznam súborov z priečinka (v tomto príklade CSV súbory ). V záložke Pridať stĺpec na páske vyberte Vyvolať vlastnú funkciu v skupine Všeobecná . V okne Invoke Custom Function zadajte Output Table ako názov nového stĺpca. Vyberte názov svojej funkcie, Transform súbor, z rozbaľovacieho menu dotazu Function . Po výbere funkcie z rozbaľovacieho menu sa zobrazí parameter funkcie a môžete si vybrať, ktorý stĺpec z tabuľky použijete ako argument pre túto funkciu. Vyberte stĺpec Content ako hodnotu / argument, ktorý sa má odovzdať pre parameter súboru.

Po výbere OK sa vytvorí nový stĺpec s názvom Výstupná tabuľka . Tento stĺpec má v bunkách hodnoty tabuľky , ako je znázornené na nasledujúcom obrázku. Pre jednoduchosť odstráňte všetky stĺpce z tejto tabuľky okrem Mena a Výstupnej tabuľky.

Poznámka

Ak sa chcete dozvedieť viac o tom, ako vybrať alebo odstrániť stĺpce z tabuľky, choďte do Vybrať alebo odstrániť stĺpce.

Vaša funkcia bola aplikovaná na každý jeden riadok z tabuľky, pričom hodnoty zo stĺpca Content slúžili ako argument pre vašu funkciu. Teraz, keď sú dáta transformované do požadovaného tvaru, môžete rozbaliť stĺpec Výstupná tabuľka výberom ikony Rozbaliť. Nepoužívajte žiadne prefixy pre rozšírené stĺpce.

Môžete si overiť, že máte dáta zo všetkých súborov v priečinku, ak skontrolujete hodnoty v stĺpci Názov alebo Dátum . V tomto prípade môžete skontrolovať hodnoty zo stĺpca Dátum , pretože každý súbor obsahuje údaje len pre jeden mesiac z daného roka. Ak vidíte viac ako jeden, znamená to, že ste úspešne spojili dáta z viacerých súborov do jednej tabuľky.

Poznámka

To, čo ste doteraz čítali, je v podstate rovnaký proces, aký prebieha počas Combine súborov , len manuálne.

Odporúčame vám tiež prečítať si článok o prehľade Combine files a Combine CSV súbory , aby ste lepšie pochopili, ako funguje zážitok combine files v Power Query a akú úlohu zohrávajú vlastné funkcie.

Pridajte nový parameter k existujúcej vlastnej funkcii

Predstavte si, že existuje nová požiadavka nad rámec toho, čo ste už vybudovali. Nová požiadavka vyžaduje, aby ste pred skombinovaním súborov filtrovali údaje v nich tak, aby ste získali len riadky, kde je krajina rovná Paname.

Aby sa táto požiadavka splnila, vytvorte nový parameter nazvaný Market s textovým dátovým typom. Pre aktuálnu hodnotu zadajte hodnotu Panama.

S týmto novým parametrom vyberte dotaz na súbor Transform Sample a filtrujte pole Krajina podľa hodnoty z parametra Trh.

Poznámka

Ak sa chcete dozvedieť viac o tom, ako filtrovať stĺpce podľa hodnôt, choďte do Filter values.

Aplikovanie tohto nového kroku na váš dotaz automaticky aktualizuje funkciu Transform súboru , ktorá teraz vyžaduje dva parametre založené na dvoch parametroch, ktoré používa váš Transform Sample súbor .

Ale dotaz na CSV súbory má vedľa seba varovný signál. Teraz, keď je vaša funkcia aktualizovaná, vyžaduje dva parametre. Takže krok, pri ktorom voláte funkciu, vedie k chybovým hodnotám, pretože počas kroku Invoked Custom Function bol do funkcie Transform odoslaný len jeden z argumentov.

Na opravu chýb dvojitým kliknutím na Invoked Custom Function v aplikovaných krokoch otvoríte okno Invoke Custom Function . V parametri Market manuálne zadajte hodnotu Panama.

Teraz sa môžete vrátiť do Rozšírenej výstupnej tabuľky v aplikovaných krokoch. Skontrolujte svoj dotaz, či sa v konečnej výsledkovej sade dotazu CSV Files zobrazujú len riadky, kde je Krajina rovná Panama.

Vytvorte vlastnú funkciu z opakovane použiteľnej logiky

Ak máte viacero dotazov alebo hodnôt, ktoré vyžadujú rovnakú sadu transformácií, môžete vytvoriť vlastnú funkciu, ktorá funguje ako opakovane použiteľná logika. Neskôr je možné túto vlastnú funkciu vyvolať proti dotazom alebo hodnotám podľa vášho výberu. Táto vlastná funkcia vám môže ušetriť čas a pomôcť vám spravovať súbor transformácií na centrálnom mieste, ktoré môžete kedykoľvek upraviť.

Napríklad si predstavte dotaz, ktorý má niekoľko kódov ako textový reťazec a chcete vytvoriť funkciu, ktorá tieto hodnoty dekóduje, ako v nasledujúcej ukážkovej tabuľke:

kód
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Začnete s parametrom, ktorý má hodnotu, ktorá slúži ako príklad. Pre tento prípad je to hodnota PTY-CM1090-LAX.

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:

• Pôvod = PTY
• Cieľ = LAX
• Letecká spoločnosť = CM
• FlightID = 1090

Nasledujúci M kód demonštruje túto množinu transformácií.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Poznámka

Ak sa chcete dozvedieť viac o jazyku formuliek Power Query M, navštívte jazyk formuliek Power Query M.

Potom môžete tento dotaz premeniť na funkciu kliknutím pravým tlačidlom na dotaz a výberom Vytvoriť funkciu. Nakoniec môžete vyvolať svoju vlastnú funkciu do ktoréhokoľvek z vašich dotazov alebo hodnôt, ako je znázornené na nasledujúcom obrázku.

Po niekoľkých ďalších transformáciách môžete vidieť, že ste dosiahli požadovaný výstup a použili logiku takejto transformácie z vlastnej funkcie.