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, vytvořte vlastní funkci Power Query, která se dá opakovaně použít tolikrát, kolikrát potřebujete, může být přínosná. 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.

I když můžete ručně vytvořit vlastní funkci Power Query pomocí kódu, jak je znázorněno v tématu Principy funkcí Power Query M, uživatelské rozhraní Power Query nabízí funkce, které vám umožní urychlit, zjednodušit a vylepšit proces vytváření a správy vlastní funkce.

Tento článek se zaměřuje na toto prostředí, které poskytuje jenom prostřednictvím uživatelského rozhraní Power Query a jak z něj získat maximum.

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 odkazu na tabulku

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í bude tento článek používat konektor složky. Další informace o konektoru složky najdete v části 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žek přejdete do složky, ve které jsou umístěné soubory, a vyberete Transformovat data nebo Upravit. Tím přejdete do prostředí Power Query. Pravým tlačítkem myši klikněte na binární hodnotu podle vašeho výběru z pole Obsah a vyberte možnost Přidat jako nový dotaz. V tomto příkladu uvidíte, že výběr byl proveden pro první soubor ze seznamu, což se stane souborem duben 2019.csv.

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

Vytvořte nový parametr s názvem Parametr souboru. Jako aktuální hodnotu použijte dotaz Ukázkový soubor, jak je znázorněno na následujícím obrázku.

Poznámka:

Doporučujeme, abyste si přečetli článek o parametrech , 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.

Typ binárního parametru se zobrazí pouze v rozevírací nabídce Typ dialogového okna Parametry, 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 Parametrsouboru. Vyberte možnost Odkaz.

Přejmenujte nově vytvořený dotaz z parametru souboru (2) na Transformovat ukázkový soubor.

Klikněte pravým tlačítkem myši na tento nový dotaz ukázkového souboru transformace a vyberte možnost Vytvořit funkci .

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

Po vytvoření funkce si všimnete, že se pro vás vytvoří nová skupina s názvem vaší funkce. Tato nová skupina bude obsahovat:

• Všechny parametry odkazované v dotazu ukázkového souboru transformace
• Dotaz ukázkového souboru transformace, který se běžně označuje jako ukázkový dotaz.
• Nově vytvořená funkce v tomto případě Transformovat soubor.

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í Transform file , takže všechny změny provedené v tomto dotazu se projeví ve funkci. To se označuje jako koncept ukázkového dotazu propojeného s funkcí.

První transformace, která se musí s tímto dotazem stát, je ta, která bude interpretovat binární soubor. V podokně náhledu můžete kliknout pravým tlačítkem myši na binární soubor a vybrat možnost CSV , která binární soubor interpretuje 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 pět a data začínají od řádku šest dolů, jak je znázorněno na následujícím obrázku.

Další sada kroků transformace, které je potřeba použít u ukázkového souboru transformace, jsou:

1. Odeberte prvních čtyři řádky – 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, najdete v části Filtrovat podle pozice řádku.

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

   

Power Query ve výchozím nastavení automaticky přidá nový krok Změněný typ po povýšení záhlaví sloupců, který automaticky rozpozná datové typy pro každý sloupec. Dotaz ukázkového souboru transformace bude vypadat jako na následujícím obrázku.

Poznámka:

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

Upozornění

Funkce transformačního souboru spoléhá na kroky provedené v dotazu ukázkového souboru transformace. Pokud se ale pokusíte kód funkce Transformovat soubor upravit ručně, zobrazí se upozornění, které čte.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. Na pásu karet na kartě Přidat sloupec vyberte možnost Vyvolat vlastní funkci ze skupiny Obecné. V okně Vyvolat vlastní funkci zadejte výstupní tabulku jako název nového sloupce. V rozevíracím seznamu Dotazu funkce vyberte název 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 parametru File.

Po výběru ok se vytvoří nový sloupec s názvem Výstupní tabulka . Tento sloupec obsahuje hodnoty tabulky v buňkách, jak je znázorněno na dalším obrázku. Pro zjednodušení odeberte všechny sloupce z této tabulky s výjimkou tabulky Name (Název ) a Output Table (Výstupní tabulka).

Poznámka:

Další informace o tom, jak vybrat nebo odebrat sloupce z tabulky, najdete v části Zvolit nebo odebrat sloupce.

Funkce se použila na každý řádek tabulky s použitím hodnot ze sloupce Content jako argumentu pro vaši funkci. Teď, když jsou data transformována na obrazec, který hledáte, můžete rozbalit sloupec Výstupní tabulka , jak je znázorněno na obrázku níže, aniž byste pro rozbalené sloupce používali předponu.

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

Poznámka:

Zatím jste si přečetli stejný proces, který se děje během kombinování souborů , ale provádí se ručně.

Doporučujeme, abyste si také přečetli článek o kombinování souborů s přehledem a kombinování souborů CSV, abyste lépe pochopili, jak funguje prostředí 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 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á Panamě.

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

Pomocí tohoto nového parametru vyberte dotaz Ukázkový soubor transformace a vyfiltrujte pole Země pomocí hodnoty z parametru Market .

Poznámka:

Další informace o filtrování sloupců podle hodnot najdete v části Filtrování hodnot.

Při použití tohoto nového kroku v dotazu se automaticky aktualizuje funkce transformačního souboru , která teď bude vyžadovat dva parametry založené na dvou parametrech, které váš ukázkový soubor transformace používá.

Dotaz na soubory CSV má vedle něj upozornění. Teď, když je vaše funkce aktualizovaná, vyžaduje dva parametry. Takže krok, ve kterém funkci vyvoláte, má za následek chybové hodnoty, protože během vyvolaného kroku vlastní funkce byl předán pouze jeden z argumentů funkci Transformovat.

Chyby opravíte tak, že poklikáním na Vyvolání vlastní funkce v použitých krocích otevřete okno Vyvolat vlastní funkci . Do parametru Market zadejte hodnotu Panama ručně.

Teď můžete zkontrolovat dotaz a ověřit, že se v konečné sadě výsledků dotazu CSV Files zobrazí jenom řádky, ve kterých se země rovná Panamě.

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:

code
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ě se jedná o hodnotu 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 na několik komponent:

• Origin = PTY
• Cíl = LAX
• Airline = CM
• FlightID = 1090

Níže je uvedený kód jazyka M pro 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 jazyce 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 využili logiku pro takovou transformaci z vlastní funkce.