Bulk-extracting Power Query M-code from multiple pbix files in Power BI

If you want to audit or analyse the M-code of multiple Power BI pbix-files at once, you start with either:

  1. a from-folder query where you filter all files of interest or
  2. a table with the full file-path-specification of the files to be analysed in “Column1”.

Then you add a column where you call the function that extracts the M-code:

Function to extract the M-code

This code is a variation of Igors function which retrieves the code from an opened pbix-file. So now you can apply it to closed pbix-files as well.

For method 1 you call it like so (as it takes the full string for the file-path as its parameter):

MQueriesPBIX([Folder Path]&[Name])

And for method 2 like so:

MQueriesPBIX([Column1])

This returns a table with one row per code-line.

Function to identify query- & stepnames

The following function processes this further and adds columns with the query- & step-names for further analysis:

You call it within an added column again, with the name of the previously created column containing the code (“Code”) like this:

MetaQueriesPBIX([Code])

This would be much easier, if we had a proper API like requested here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7345565-power-bi-designer-api 

That API would also enable us to bulk-retrieve other useful information from the file like everything about the DAX data model like it is currently possible with .bim-files from tabular models hosted on SSAS (blogpost with details to follow). So please vote for that feature if you find it useful as well!!

Enjoy & stay queryious 🙂

Create a function library in Power BI using M-Extensions

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

  1. Your functions: Number.Double and Number.Triple
  2. Combined with the keyword “shared” and separated by “;”
  3. 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

Read more