Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » Query-editor » Vlastni-funkce-UDF-Power-Query-Excel
Microsoft Excel logo

Vlastní funkce UDF v Power Query Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na vlastní funkce v Power Query. Aneb jak využít M-language pro tvorbu vlastních funkcí, které pak využijete při úpravě vašich tabulek.

Úvodem do vlastních funkcí

I v Power Query (ať už pod Excel, tak i v Power BI) se dají tvořit vlastní funkce, které následně využijete při zpracovávání tabulek. Vím, že v Power Query je k dispozici množství funkci, ale proč si nevytvořit svou? Jak na to se dozvíte v tomto článku a nejen to, třeba i jak tuto funkci využít při úpravě tabulky (tabulek). Opět pro přehlednost bude článek rozdělen na jednotlivé kapitoly:


Zdrojová data

Pro vyzkoušení potřebujete tabulka, já budu předvádět na podobné tabulce:

Data pro PowerQuery Excel

Trocha teorie

Potřebuji-li do Power Query dostat valstní funkci (například ještě v seznamu funkci není k dispozici). Ukáži na jednoduchých příkladech. Například potřebuji zobrazit konstantu 42 (třeba odpověď na nejdůležitější otázku lidstva).

Neboli do funkce (název dle dotazu více později) s názvem Func je součásti vlastní funkce. Do dané funkce je možno vložit parametry - jsou v závorce a následně se přiřadí přes rovná se a znaménko menší => se přiřadí požadovaný výpočet (konstatna):

let Func = () => 42 in Func()

Nebo textová konstanta:

let Func = () => "Konstanta" in Func()

Jak umístit funkci do M-kódu je prakticky ukázáno v další kapitole. Další možnost provést výpočet s parametrem:

let Func = (x) => x + 0 in Func(42)

Případně pokud zadání parametru není povinné:

let Func = (optional x) => if x is null then "42" else x in Func(5)

Dále je možno mít parametr deklarovaný, neboli co se může v parametru objevit, případně jakého formátu bude výsledek. Podrobněji o této problematice bude popsáno v samostatném článku.

(x as text) as table

Bohužel na tuto funkci se pak v Power Query nedá jednoduše odkázat. Neboli většinou potřebujete do funkce vkládat argumenty, které pak provedou výpočet:

Neboli funkci obalíme přes let možností přiřadit parametry:

Syntaxe

Obecná syntaxe

let Func = (x) => let // výpočet in // návratová hodnota in Func

Ukázka pro výpočet Objemu kvádru

let Func = (x,y,z) => let Func = (x * y * z) in Func in Func

Pokud stále přemýšlíte, jak ještě funkci přidat patřičný název (název pro prázdný dotaz). V další kapitole je popsáno jak na to.

Více o M-language jsem sepsal v samostatném článku (článcích):

Vlastní funkce

Funkci napsat umíme, teorií máte za sebou. Teď jen tuto funkci musíme dostat do Power Query. Je to jednoduché, máte-li spuštěný Power Query Editor, stačí jen vložit prázdný dotaz. Z menu Domovská stránka > Nový zdroj > Jiné zdroje > Prázdný dotaz

Prazdný dotaz - Power Query - Excel

Přidá se prázdný dotaz pod jménem Dotaz*, tento dotaz si můžete pojmenovat třeba Konstanta_42

Prazdný dotaz - Power Query - Excel

Mate-li přejmenováno je potřeba otevřít rozšířený editor. Domovská stránka > Rozšířený editor

Rozšířený editor - Power Query - Excel

Následně jen stačí do Rozšířeného editoru vložit následující funkci:

let Func = () => 42 in Func()

Ukázka:

Rozšířený editor - Power Query - Excel

Klikem na OK se konstanta propíše do PowerQuery.

Funkce s parametrem

Ať se na funkci mohu odkazovat.

Podobně můžeme vložit parametrickou funkci pro výpočet objemu kvádru. Například pod jménem: Funkce_Objem_Kvadr

let Func = (x,y,z) => let Func = (x * y * z) in Func in Func

Po vložení bude Rozšířený editor vypadat podobně:

Rozšířený editor - Power Query - Excel

Po kliku na OK již bude možno do funkce zadávat parametry:

Rozšířený editor - Power Query - Excel

Jak se na funkci odvolat proberu v následujícím článku.

Použití funkce na tabulce

Předpokládám, že mám funkci Odmena, která vypočte výši odměny ze zisku. Odměna je 10%.

let Odmena = (Zisk) => let Odmena = Zisk * 0.1 in Odmena in Odmena

Tip. Funkce v M-language se nemusí jmenovat jen Func, ale v kódu si názvy a argumenty můžeme nazvat jak potřebujete. Funkce získají na přehlednosti a jasnosti.

Následně do tabulky chci přidat vlastní funkci, která vypočte odměnu ze sloupce Cena (oněch 10%). Na menu Přidání sloupce klik na ikonu Vyvolat vlastní funkci.

Po kliku na OK již bude možno do funkce zadávat parametry:

Přidání sloupce - Power Query - Excel

Obdržíte dialogové okno:

Funkce - Power Query - Excel

Vyplníte Nový název sloupce a Dotaz na funkci. Vyberete ze seznamu dostupných vlastních funkcí.

Funkce - Power Query - Excel

Klikem na OK se přidá výpočtový sloupec s vlastní funkcí.

Vlastní sloupec nad tabulkou

Druhá možnost je použít funkci přes Vlastní sloupec. Na menu Přidání sloupce klik na ikonu Vlastní sloupec.

V dialogovém okně vyplnit o název slupce a vzorec.

Odmena([Cena]) Funkce - Power Query - Excel

Tip: Po kliku na OK se přidá sloupec a z Vlastního sloupce se stane Vyvolat vlastní funkce (uvidíte v nastavení dotazů).

Ukázky funkcí

V přípravě další funkce, které se dají využít v PowerQuery. Pokud vás napadá nějaká zajímavá funkce, můžete se zmínit v komentářích.

Související články

Na co se asi budete chtít podívat, aneb další témata související s Power Query:


Závěrem

Napadá vás něco co do článku ještě doplnit? Můžete využít komentáře.


Článek byl aktualizován: 12.12.2019 11:43

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.






Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Reklama


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2019 | 252

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.