Používanie vlastný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.

Hoci môžete vytvoriť vlastnú funkciu doplnku Power Query manuálne pomocou kódu, ako je popísané v téme Pochopenie funkcií jazyka Power Query M, používateľské rozhranie Power Query vám ponúka funkcie na urýchlenie, zjednodušenie a vylepšenie procesu vytvárania a spravovania vlastnej funkcie.

Tento článok sa zameriava na toto prostredie, ktoré je k dispozícii iba prostredníctvom používateľského rozhrania Power Query a na to, ako z neho vyťažiť čo najviac.

Dôležité

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

Vytvorenie vlastnej funkcie z odkazu na tabuľku

Poznámka

Nasledujúci príklad bol vytvorený pomocou prostredia pracovnej plochy, ktoré sa nachádza v aplikácii Power BI Desktop, a možno ho použiť aj pomocou prostredia Power Query, ktoré sa nachádza v Exceli pre Windows.

V súlade s týmto príkladom si môžete stiahnuť vzorové súbory použité v tomto článku z nasledujúceho prepojenia na stiahnutie. Pre jednoduchosť tento článok používa konektor priečinka. Ďalšie informácie o konektore priečinka nájdete v téme Priečinok. Cieľom tohto príkladu je vytvoriť vlastnú funkciu, ktorú možno použiť na všetky súbory v danom priečinku predtým, ako skombinuje všetky údaje zo všetkých súborov do jednej tabuľky.

Začnite s použitím konektora priečinka a prejdite do priečinka, v ktorom sa vaše súbory nachádzajú, a vyberte položku Transformovať údaje alebo Upraviť. Tieto kroky vás vezmú do prostredia Power Query. Kliknite pravým tlačidlom myši na binárnu hodnotu vybratú v poli Obsah a vyberte možnosť Pridať ako nový dotaz. V tomto príklade sa vybral prvý súbor zo zoznamu, ktorý je súborom apríla 2019.csv.

Táto možnosť efektívne vytvorí nový dotaz s krokom navigácie priamo na tento súbor ako binárny údaj a názov tohto nového dotazu je cesta k súboru vybratého súboru. Premenujte tento dotaz na Vzorový súbor.

Vytvorte nový parameter s názvom Parameter súboru a typom binárnej hodnoty. Dotaz Vzorový súbor použite 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 doplnku Power Query.

Vlastné funkcie je možné vytvoriť pomocou ľubovoľného typu parametrov. Pre žiadnu vlastnú funkciu sa nevyžaduje, aby mala binárny údaj ako parameter.

Typ binárneho parametra sa zobrazí v rozbaľovacej ponuke Typ v dialógovom okne Parametre, keď máte dotaz, ktorý sa vyhodnotí ako binárny.

Bez parametra je možné vytvoriť vlastnú funkciu. Bežne sa to zobrazuje v prípadoch, kde je možné odvodiť vstup z prostredia, kde sa práve vyvoláva funkcia. Napríklad funkcia, ktorá použije aktuálny dátum a čas prostredia a z týchto hodnôt vytvorí konkrétny textový reťazec.

Kliknite pravým tlačidlom myši na položku Parameter súboru na table Dotazy. Vyberte možnosť Odkaz.

Premenujte novovytvorený dotaz z parametra súbor (2) na Transformovať vzorový súbor.

Kliknite pravým tlačidlom myši na tento nový dotaz transformovať vzorový súbor a vyberte možnosť Vytvoriť funkciu .

Táto operácia efektívne vytvorí novú funkciu, ktorá je prepojená s dotazom Transformovať vzorový súbor . Všetky zmeny, ktoré vykonáte v dotaze Transformovať vzorový súbor , sa automaticky replikujú do vašej vlastnej funkcie. Pri vytváraní tejto novej funkcie použite ako názov funkcie transformovať súbor.

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

• Všetky parametre, na ktoré sa odkazovalo v dotaze Transformovať vzorový súbor .
• Váš dotaz transformovať vzorový súbor , bežne známy ako vzorový dotaz.
• Vaša novovytvorená funkcia, v tomto prípade Transformovať súbor.

Použitie transformácií na vzorový dotaz

Keď ste vytvorili novú funkciu, vyberte dotaz s názvom Transformovať vzorový súbor. Tento dotaz je teraz spojený s funkciou Transformovať súbor , takže všetky zmeny vykonané v tomto dotaze sa prejavia vo funkcii. Toto pripojenie sa označuje ako koncept ukážkového dotazu prepojeného na funkciu.

Prvým transformáciou, ktorá sa musí uskutočniť s týmto dotazom, je transformácia, ktorá interpretuje binárnu hodnotu. Môžete kliknúť pravým tlačidlom myši na binárny údaj z tably ukážky a vybrať možnosť CSV na interpretáciu binárneho údaja ako súboru CSV.

Formát všetkých CSV súborov v priečinku je rovnaký. Všetky majú hlavičku, ktorá presahuje prvé štyri najvrchnejšie riadky. Hlavičky stĺpcov sa nachádzajú v riadku 5 a údaje začínajú smerom nadol z riadka 6, ako je to znázornené na nasledujúcom obrázku.

Ďalšou množinou krokov transformácie, ktoré je potrebné použiť na súbor Ukážka transformácie, sú tieto:

1. Odstráňte štyri najvrchnejšie riadky – táto akcia zbaví riadkov, ktoré sú považované za súčasť časti hlavičky súboru.

   

   Poznámka

   Ďalšie informácie o tom, ako odstrániť riadky alebo filtrovať tabuľku podľa pozície riadka, nájdete v téme Filtrovanie podľa pozície riadka.

2. Zvýšenie úrovne hlavičiek – hlavičky pre finálnu tabuľku sú teraz v prvom riadku tabuľky. Môžete ich propagovať tak, ako je to znázornené na nasledujúcom obrázku.

   

Po propagovaní hlavičiek stĺpcov Power Query predvolene automaticky pridá nový krok Zmenený typ , ktorý automaticky rozpozná typy údajov pre každý stĺpec. Váš dotaz Transformovať vzorový súbor vyzerá ako na nasledujúcom obrázku.

Poznámka

Ďalšie informácie o tom, ako zvýšiť úroveň a znížiť úroveň hlavičiek, nájdete v téme Zvýšenie úrovne alebo zníženie úrovne hlavičiek stĺpcov.

Výstraha

Funkcia Transformovať súbor využíva kroky vykonané v dotaze Transformovať vzorový súbor . Ak sa však pokúsite manuálne upraviť kód funkcie Transformovať súbor , zobrazí sa upozornenie s textom 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'.

Vyvolanie vlastnej funkcie ako nového stĺpca

Vďaka teraz vytvorenej vlastnej funkcii a zahrnutiu všetkých krokov transformácie sa môžete vrátiť do pôvodného dotazu, kde máte zoznam súborov z priečinka (v tomto príklade je to CSV súbory ). Na karte Pridať stĺpec na páse s nástrojmi vyberte položku Vyvolať vlastnú funkciu v skupine Všeobecné . V okne Vyvolať vlastnú funkciu zadajte výstupnú tabuľku ako názov nového stĺpca. Vyberte názov funkcie Transformovať súbor v rozbaľovacom zozname Dotaz na funkciu. Po výbere funkcie v rozbaľovacej ponuke sa zobrazí parameter funkcie a ako argument tejto funkcie môžete vybrať stĺpec z tabuľky. Vyberte stĺpec Content (Obsah) ako hodnotu alebo argument, ktorý sa má odovzdať do parametra Súbor.

Po výbere tlačidla OK sa vytvorí nový stĺpec s názvom Output Table (Výstupná tabuľka ). Tento stĺpec má v bunkách hodnoty Tabuľky , ako je to znázornené na nasledujúcom obrázku. Z dôvodu zjednodušenia odstráňte všetky stĺpce z tejto tabuľky okrem stĺpcov Name (Názov ) a Output Table (Výstup).

Poznámka

Ďalšie informácie o tom, ako vybrať alebo odstrániť stĺpce z tabuľky, nájdete v téme Výber alebo odstránenie stĺpcov.

Vaša funkcia sa použila na každý riadok z tabuľky pomocou hodnôt zo stĺpca Obsah ako argument pre vašu funkciu. Teraz, keď sa údaje transformujú do tvaru, ktorý hľadáte, môžete výberom ikony Rozbaliť rozbaliť stĺpec Výstupná tabuľka . Pre rozbalené stĺpce nepoužívajte žiadnu predponu.

To, či máte údaje zo všetkých súborov v priečinku, môžete overiť tak, že 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 len údaje za jeden mesiac daného roka. Ak sa zobrazia viaceré, znamená to, že úspešne skombinujete údaje z viacerých súborov do jednej tabuľky.

Poznámka

Proces, ktorý ste doposiaľ čítali, prebieha v podstate rovnako ako pri funkcii Skombinovať súbory , ale vykonáva sa manuálne.

Odporúčame vám tiež prečítať si článok o funkciách Skombinovať súbory aKombinovať súbory CSV, aby ste lepšie porozumeli spôsobu fungovania funkcie Skombinovať súbory v Power Query a roly, ktoré sa prehrávajú vlastné funkcie.

Pridanie nového parametra k existujúcej vlastnej funkcii

Predstavte si, že nad tým, čo ste v súčasnosti vytvorili, existuje nová požiadavka. Nová požiadavka vyžaduje, aby pred kombinovaním súborov, môžete filtrovať údaje vnútri nich len získať riadky, kde krajina sa rovná Panama.

Ak chcete, aby sa táto požiadavka stala, vytvorte nový parameter s názvom Trh s textovým typom údajov. Do poľa Aktuálna hodnota zadajte hodnotu Panama.

Pomocou tohto nového parametra vyberte dotaz Transformovať vzorový súbor a vyfiltrujte pole Country (Krajina ) pomocou hodnoty z parametra Market (Trh ).

Poznámka

Ďalšie informácie o filtrovaní stĺpcov podľa hodnôt nájdete v téme Filtrovanie hodnôt.

Pri použití tohto nového kroku v dotaze sa automaticky aktualizuje funkcia Transformovať súbor , ktorá teraz vyžaduje dva parametre na základe dvoch parametrov, ktoré použije váš súbor Transformovať ukážku.

Pri dotaze na súbory CSV sa však nachádza znamienko upozornenia. Teraz, keď je funkcia aktualizovaná, vyžaduje dva parametre. Takže krok, pri ktorom vyvoláte funkciu, má za následok chybové hodnoty, pretože len jeden z argumentov bol prenesený do funkcie Transformovať súbor počas kroku Vyvolaná vlastná funkcia .

Ak chcete chyby opraviť, dvakrát kliknite na položku Vyvolaná vlastná funkcia v okne Použité kroky , čím otvoríte okno Vyvolať vlastnú funkciu . V parametri Market (Trh) manuálne zadajte hodnotu Panama.

Teraz sa môžete v časti Použité kroky vrátiť k rozšírenej výstupnej tabuľke. Skontrolujte dotaz a overte, či sa v konečnom množine výsledkov dotazu CSV Files zobrazia iba riadky, v ktorých sa zobrazuje krajina rovná hodnote Panama.

Vytvorenie vlastnej funkcie z opätovne použiteľnej logiky

Ak máte viacero dotazov alebo hodnôt, ktoré vyžadujú rovnakú množinu transformácií, môžete vytvoriť vlastnú funkciu, ktorá funguje ako opätovne použiteľný kus logiky. Neskôr je možné túto vlastnú funkciu vyvolať na dotazy alebo hodnoty podľa vášho výberu. Táto vlastná funkcia vám môže ušetriť čas a pomôcť vám pri spravovaní množiny transformácií na centrálnom mieste, ktoré môžete kedykoľvek upraviť.

Predstavte si napríklad dotaz, ktorý má viacero kódov ako textový reťazec a chcete vytvoriť funkciu, ktorá tieto hodnoty dekóduje tak, ako je to 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 tým, že s parametrom, ktorý obsahuje hodnotu, ktorá slúži ako príklad. V tomto prípade 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:

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

Nasledujúci kód jazyka M ukazuje 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

Ďalšie informácie o jazyku vzorcov Power Query M nájdete v téme Jazyk vzorcov Power Query M.

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 požadovaný výstup a použili logiku takejto transformácie z vlastnej funkcie.