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):
- Your functions: Number.Double and Number.Triple
- Combined with the keyword “shared” and separated by “;”
- Prefix by “section MyLibrary” gives this text:
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.
- 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.
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 🙂