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
- copy the text to a text editor
- save it as “MyLibrary.pq”
- open file explorer, copy the file and zip it
- rename the zip to MyLibrary.mez (ignore the warning)
- 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
- restart PBID
- 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. But plans to provide it for Excel as well have been made public already.
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 🙂