Použití vlastních funkcí

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

Vlastní funkci Power Query můžete vytvořit ručně pomocí jazyka vzorců Power Query Mnebo uživatelské rozhraní Power Query nabízí funkce pro zrychlení, zjednodušení a vylepšení procesu vytváření a správy vlastní funkce.

Nejprve probereme základní kroky pro vytvoření vlastní funkce pomocí kódu vuživatelském rozhraní, poté se zaměříme na to, jak pomocí rozhraní přeměnit složité akce na funkci, kterou lze opakovaně použít.

Důležitý

Tento článek popisuje, jak vytvořit vlastní funkci s Power Query pomocí běžných transformací přístupných v uživatelském rozhraní Power Query. Zaměřuje se na základní koncepty vytváření vlastních funkcí a odkazy na další články v dokumentaci k Power Query, kde najdete další informace o konkrétních transformech, na které se odkazuje v tomto článku.

Vytvoření vlastní funkce z kódu v uživatelském rozhraní

Poznámka

V Power BI Desktopu nebo pomocí prostředí Power Query, které najdete v Excelu pro Windows, můžete postupovat podle následujících kroků.

1. Pomocí prostředí konektoru se připojte k datům, kde jsou uložená. Po výběru dat vyberte tlačítko Transformovat data nebo Upravit. Tím přejdete do prostředí Power Query.
2. Klikněte pravým tlačítkem na prázdné místo v podokně Dotazy vlevo.
3. Vyberte prázdný dotaz.
4. V novém prázdném okně dotazu vyberte nabídku Domů a pak Rozšířený editor.
5. Nahraďte šablonu vlastní funkcí. Například:

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

6. Vyberte Hotovo.

Další informace o vývoji vlastních funkcí pomocí jazyka vzorců Power Query M najdete v tomto článku: Principy funkcí Power Query M. V následujících částech najdete kurzy popisující, jak můžete pomocí uživatelského rozhraní Power Query vyvíjet vlastní funkce bez psaní kódu a pokyny k vyvolání vlastní funkce v dotazu.

Vytvoření vlastní funkce z referenčního kurzu k tabulce

Poznámka

Následující příklad byl vytvořen pomocí desktopového prostředí nalezeného v Power BI Desktopu a můžete ho také sledovat pomocí prostředí Power Query, které najdete v Excelu pro Windows.

Můžete postupovat podle tohoto příkladu stažením ukázkových souborů použitých v tomto článku z následujícího odkazu ke stažení. Pro zjednodušení tento článek používá konektor Folder. Další informace o konektoru složky najdete pod částí Složka. Cílem tohoto příkladu je vytvořit vlastní funkci, která se dá použít u všech souborů v této složce, než zkombinujete všechna data ze všech souborů do jedné tabulky.

Začněte tím, že pomocí prostředí konektoru složky přejdete do složky, ve které jsou umístěné soubory, a vyberte Transformovat data nebo Upravit. Tyto kroky vás převezmou do prostředí Power Query. Klikněte pravým tlačítkem myši na hodnotu Binární, kterou jste vybrali, z pole Obsah a vyberte možnost Přidat jako nový dotaz. V tomto příkladu byl výběr proveden pro první soubor ze seznamu, který je právě souborem duben 2019.csv.

Tato možnost efektivně vytvoří nový dotaz s navigačním krokem přímo do tohoto souboru ve formě binárního souboru a název tohoto nového dotazu je cesta k vybranému souboru. Přejmenujte tento dotaz na ukázkový soubor.

Vytvořit nový parametr s názvem File Parameter a typem Binary. Použijte dotaz z ukázkového souboru jako výchozí hodnotu a aktuální hodnotu .

Poznámka

Doporučujeme, abyste si přečetli článek o parametry, abyste lépe pochopili, jak vytvářet a spravovat parametry v Power Query.

Vlastní funkce lze vytvořit pomocí libovolného typu parametrů. Není nutné, aby každá vlastní funkce měla binární hodnotu jako parametr.

Binární typ parametru se zobrazí pouze v dialogovém okně ParametryTyp rozevírací nabídce, pokud máte dotaz, který se vyhodnotí jako binární.

Vlastní funkci je možné vytvořit bez parametru. To se běžně projevuje ve scénářích, kdy je možné odvodit vstup z prostředí, ve kterém se funkce vyvolává. Například funkce, která přebírá aktuální datum a čas prostředí, a vytvoří z těchto hodnot konkrétní textový řetězec.

V podokně dotazy klikněte pravým tlačítkem na Parametr souboru. Vyberte možnost Reference .

Přejmenujte nový dotaz z Parametr souboru (2) na Transformace ukázkového souboru.

Klikněte pravým tlačítkem myši na tento nový transformační vzorový soubor dotazu a vyberte možnost Vytvořit funkci.

Tato operace efektivně vytvoří novou funkci, která propojuje s transformovaným ukázkovým souborem , dotazem. Všechny změny provedené v ukázkovém souboru transformace dotazu se automaticky replikují do vlastní funkce. Při vytváření této nové funkce použijte transformační soubor jako název funkce.

Po vytvoření funkce si všimněte, že se pro vás vytvoří nová skupina s názvem vaší funkce. Tato nová skupina obsahuje:

• Všechny parametry, které byly odkazovány ve vašem ukázkovém souboru Transform dotaz.
• Váš soubor transformace ukázkového dotazu, který se běžně označuje jako ukázkový dotaz .
• Vaše nově vytvořená funkce, v tomto případě transformace souboru .

Použití transformací na ukázkový dotaz

Po vytvoření nové funkce vyberte dotaz s názvem Transform Sample File. Tento dotaz je teď propojený s funkcí Transformovat soubor, takže všechny změny provedené v tomto dotazu se projeví ve funkci. Toto připojení se označuje jako koncept ukázkového dotazu propojeného s funkcí.

První transformace, která se musí s tímto dotazem provést, je ta, která interpretuje binární soubor. V podokně náhledu můžete kliknout pravým tlačítkem na binární soubor a vybrat možnost CSV interpretovat binární soubor jako soubor CSV.

Formát všech souborů CSV ve složce je stejný. Všechny mají záhlaví, které pokrývá první čtyři řádky. Záhlaví sloupců jsou umístěná v řádku 5 a data začínají od řádku 6 směrem dolů, jak je znázorněno na následujícím obrázku.

Další sada kroků transformace, které je potřebné aplikovat na ukázkový soubor transformace , je následující:

1. Odebrání prvních čtyř řádků– tato akce odstraní řádky, které jsou považovány za součást oddílu záhlaví souboru.

   

   Poznámka

   Další informace o tom, jak odebrat řádky nebo filtrovat tabulku podle pozice řádku, přejděte na Filtrovat podle pozice řádku.

2. Zvýšit úroveň záhlaví– záhlaví pro konečnou tabulku jsou teď na prvním řádku tabulky. Můžete je propagovat, jak je znázorněno na dalším obrázku.

   

Po zvýšení úrovně záhlaví sloupců Power Query ve výchozím nastavení automaticky přidá nový Změněný typ krok, který automaticky rozpozná datové typy pro každý sloupec. Váš dotaz na soubor vzorku transformace vypadá jako na následujícím obrázku.

Poznámka

Další informace o zvýšení úrovně a snížení úrovně záhlaví najdete v tématu Zvýšení úrovně nebo snížení úrovně záhlaví sloupců.

Opatrnost

Funkce transformovacího souboru spoléhá na kroky provedené v ukázkovém souboru dotazu. Pokud se ale pokusíte ručně upravit kód pro funkci v souboru Transform, zobrazí se vám upozornění, že 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'.

Vyvolání vlastní funkce jako nového sloupce

Když je teď vlastní funkce vytvořená a všechny kroky transformace jsou zahrnuté, můžete se vrátit k původnímu dotazu, kde máte seznam souborů ze složky (soubory CSV v tomto příkladu). V kartě pásu karet Přidat sloupec vyberte Vyvolat vlastní funkci ze skupiny Obecné. V okně Vyvolat vlastní funkci zadejte Výstupní tabulka jako Název nového sloupce. V rozevíracím seznamu dotazu Funkce vyberte název své funkce, Transformovat soubor. Po výběru funkce z rozevírací nabídky se zobrazí parametr funkce a můžete vybrat sloupec z tabulky, který chcete použít jako argument pro tuto funkci. Vyberte sloupec Content jako hodnotu nebo argument, který se má předat pro parametr souboru .

Po výběru OKse vytvoří nový sloupec s názvem Výstupní Tabulka. Tento sloupec má v buňkách tabulky hodnoty, jak je znázorněno na následujícím obrázku. Pro zjednodušení odeberte všechny sloupce z této tabulky kromě Name a Výstupní tabulka.

Poznámka

Chcete-li se dozvědět více o tom, jak zvolit nebo odebrat sloupce z tabulky, zamiřte na Zvolit nebo odebrat sloupce.

Vaše funkce byla použita na každý jednotlivý řádek tabulky, přičemž hodnoty ze sloupce Content byly použity jako argument. Teď, když jsou data transformována na obrazec, který hledáte, můžete rozbalit výstupní tabulku sloupce výběrem ikony Rozbalit. Nepoužívejte pro rozbalené sloupce žádnou předponu.

Zkontrolujte hodnoty ve sloupci Název nebo Date, abyste ověřili, že máte data ze všech souborů ve složce. V tomto případě můžete zkontrolovat hodnoty ze sloupce Date, protože každý soubor obsahuje jenom data pro jeden měsíc z daného roku. Pokud se zobrazí více souborů, znamená to, že jste úspěšně zkombinovali data z více souborů do jedné tabulky.

Poznámka

To, co jste si zatím přečetli, je v zásadě stejný proces, který se děje během zkušenosti Kombinování souborů, ale provádí se ručně.

Doporučujeme, abyste si také přečetli článek o Přehled kombinování souborů a Kombinování souborů CSV, abyste lépe pochopili, jak funguje kombinování souborů v Power Query a jakou roli hrají vlastní funkce.

Přidání nového parametru do existující vlastní funkce

Představte si, že nad tím, co jste právě vytvořili, existuje nový požadavek. Nový požadavek vyžaduje, abyste před kombinováním souborů vyfiltrovali data uvnitř, abyste získali pouze řádky, ve kterých se země rovná Panama.

Aby k tomuto požadavku došlo, vytvořte nový parametr s názvem Market s textovým datovým typem. Do pole Aktuální hodnotazadejte hodnotu Panama.

Pomocí tohoto nového parametru vyberte transformační ukázkový soubor dotazu a vyfiltrujte pole Země pomocí hodnoty z parametru Market.

Poznámka

Další informace o filtrování sloupců podle hodnot najdete v tématu Filtrování hodnot.

Použití tohoto nového kroku na dotaz automaticky aktualizuje funkci Transformovat soubor, která teď vyžaduje dva parametry založené na dvou parametrech, které váš ukázkový soubor transformace používá.

U souborů CSV dotaz má vedle něj upozornění. Teď, když je vaše funkce aktualizovaná, vyžaduje dva parametry. Během kroku, kdy je vyvolána vlastní funkce , dochází k chybovým hodnotám, protože do funkce transformování souboru byl předán pouze jeden argument.

Chyby opravíte tak, že poklepete na Vyvolání vlastní funkce v části Použité kroky, čímž otevřete okno Vyvolat vlastní funkci. Do parametru Market zadejte hodnotu Panamaručně .

Nyní se můžete vrátit do rozšířené výstupní tabulky v použitých krocích. Zkontrolujte svůj dotaz a ověřte, že se v konečné sadě výsledků dotazu soubory CSV zobrazí jenom řádky, kde země je rovna Panama.

Vytvoření vlastní funkce z opakovaně použitelné části logiky

Pokud máte více dotazů nebo hodnot, které vyžadují stejnou sadu transformací, můžete vytvořit vlastní funkci, která funguje jako opakovaně použitelná část logiky. Později můžete tuto vlastní funkci vyvolat pro dotazy nebo hodnoty podle vašeho výběru. Tato vlastní funkce vám může ušetřit čas a pomoct vám při správě sady transformací v centrálním umístění, které můžete kdykoli změnit.

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, jako v následující ukázkové tabulce:

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

Začnete tím, že budete mít parametr, který má hodnotu, která slouží jako příklad. V tomto případě je to hodnota PTY-CM1090-LAX.

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 do několika komponent:

• Původ = PTY
• Destinace = LAX
• Airline = CM
• FlightID = 1090

Následující M kód předvádí tuto sadu transformací.

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

Další informace o jazyce vzorců Power Query M najdete v tématu jazyk vzorců Power Query M.

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 použili logiku pro takovou transformaci z vlastní funkce.