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 popsáno v tématu Vysvětlení 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í tento článek používá 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. Tyto kroky vás převezmou 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 byl výběr proveden pro první soubor ze seznamu, který 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í a název tohoto nového dotazu je cesta k souboru vybraného souboru. Přejmenujte tento dotaz na ukázkový soubor.
Vytvořte nový parametr s názvem File Parameter a typem Binary. Jako výchozí hodnotu a aktuální hodnotu použijte dotaz Ukázkový soubor.
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 Parametr souboru. 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á je propojená 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šimněte, že se pro vás vytvoří nová skupina s názvem vaší funkce. Tato nová skupina obsahuje:
- 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. 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 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 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řeba použít u ukázkového souboru transformace, jsou:
Odeberte prvních čtyři řádky – tato akce se zbaví řádků, 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.
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.
Po zvýšení úrovně záhlaví sloupců Power Query ve výchozím nastavení automaticky přidá nový krok Změněný typ , který automaticky rozpozná datové typy pro každý sloupec. Dotaz ukázkového souboru transformace vypadá jako další obrázek.
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
S nově vytvořenou vlastní funkcí a všemi kroky transformace, které jsou součástí, se můžete vrátit k původnímu dotazu, kde máte seznam souborů ze složky (v tomto příkladu soubory CSV). 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 transformovaná na obrazec, který hledáte, můžete rozbalit sloupec Výstupní tabulka výběrem ikony Rozbalit. Nepoužívejte pro rozbalené sloupce žádnou 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 se zobrazí 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 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á 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ď vyžaduje 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ě.
Nyní se můžete vrátit k rozbalené výstupní tabulce v použitých krocích. Zkontrolujte dotaz a ověřte, že se v konečné sadě výsledků dotazu CSV Files zobrazí jenom řádky, ve kterých je země rovna 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ě 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 na několik komponent:
- Origin = PTY
- Cíl = LAX
- Airline = CM
- FlightID = 1090
Následující kód M ukazuje, že sada 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 použili logiku pro takovou transformaci z vlastní funkce.
