Having the ability to use own M-function the same way than native functions in Power BI and Excel has been one of my biggest wishes for quite some time. So I was more than amazed to see Frank Tonsen’s comment showing a way to do exactly this in PowerBI, that has been available for almost half a year now: M-Extensions as part of the custom connectors.
Unlike custom connectors who show up in the import-dialogue and provide a custom tailored option for importing data or creating queries, M-Extensions don’t show up explicitly anywhere in Power BI: They just do their M(agic) job to make the functions that you’ve defined in them accessible, as if they were inbuilt native functions: Type their name into the formula bar like this (1):
And enjoy the function description (2: if you’ve specified it in the definition, which is optional):
Simplest example
- Your functions: Number.Double and Number.Triple
- Combined with the keyword “shared” and separated by “;”
- Prefix by “section MyLibrary” gives this text:
section MyLibrary;shared Number.Double = (Number as number) =>2 * Number;shared Number.Triple = (Number as number) =>3 * Number;
How to make M-Extensions work
The following steps have been updated at 2018-July-04 (no need to restart PBID after you’ve added or updated mez-files):
- copy the text to a text editor
- save it as “MyLibrary.mez”
- copy this file to the folder: C:\Users\<YourUserName>\Documents\Microsoft Power BI Desktop\Custom Connectors (or like described in here)
- if you haven’t already: enable custom connector option in the preview features
- use the functions
- dance the happy dance
What I also learned from Frank was that we even don’t need Visual Studio for it here, so this is really lightweight.
Limitations
- This currently just works for Power BI Desktop and via gateways in the Power BI Service. Currently they don’t work in Excel.
- Functions using #shared will not work (if you have a function with #shared in your .mez-file, the whole file will be ignored)
- The same goes for functions using R.Execute: The will make the whole .mez-file invalid.
Bonus
It’s nice that one can have multiple library.mez files in the folder, so you can only ship what’s necessary or work with external libraries without having to merge them with yours. To make this work, they have to have unique section names (like “section MyStatsLibrary” or “section IgorsCoolFunctions”). Also errors will be thrown if a function with the same name appears in more than one library. I will leave this aspect for another blogpost, as there are a couple of implications and possibilities that come with this.
Enjoy & stay queryious 🙂
imke, but if you share the pbix file, does the function works, or the new users need to install the library too ?
Hi mim,
good point: Currently, the library files (.mez) have to sit on each machine where the files shall be opened (just like the other custom connectors as well).
But as this doesn’t require an installation, you’re independent from admins 🙂
Cheers, Imke
Great post!
Does it still work after you publish the file to the Power BI service?
Thank you!
Currently, this only works in Power BI Desktop.
But if I understand this tweet correctly:
it will only be a matter of time that we get it in the service (and in Excel) as well.
There should be an online portal for custom functions, just like for custom visuals. You would be able to search and browse through those functions. Functions should have descriptions and tags. PBI would be able to automatically download each function that is explicitly referenced in your file “Variable = OnLineDepository:SomeFunction(x,y)”.
Someone already posted this idea, you can vote for this idea here:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13892073-create-user-functions
Yes, that sounds great. However, there come some implications with it when
1) these custom functions reference other custom functions and
2) people want to share different functions with the same name
I will cover these aspects in the follow-up-article here soon.
you mean something like package in R & python ? not sure what the road map is for M in the long term
@imke – I tried replicating the above procedure on a custom connector developed by Chris
https://blog.crossjoin.co.uk/2017/09/04/power-bi-custom-data-connector-for-language-detection-key-phrase-extraction-and-sentiment-analysis/
I could not get it to work
Do we need to follow a different procedure there
Hi sam,
it works for me, but I’ve built it with Visual Studio. Will check tomorrow how it works with the text-editor only.
Please be aware that this “data-connnector” again has no import-dialogue like you’d normally expect. It is just available from the function calls like shown in Chris’ sample pbix. (The queries in the group referencing comments on his blogpost didn’t work either, but as they reference local files, this isn’t to expect).
Cheers, Imke
I can confirm that the simple compression of the text-file does NOT work for custom connectors.
So you have to go via Visual Studio to create the .mez file for it.
Hi Imke,
Thanks for the article. I’ve been meaning to put my custom functions in M extension libraries (organized by category), but when I think of having to add documentation for over 100 general purpose functions…well, sigh. However, it’s got to be done, since like you, I’ve been waiting for this kind of functionality for a long time. Your article might be my wake-up call. 🙂
You’re welcome Colin!
No pain, no gain – ya know? 😉
Pingback: DAX CALCULATE Debugger – The BIccountant
Pingback: Present Value (PV) function for Power Query – The BIccountant