If you want to audit or analyse the M-code of multiple Power BI pbix-files at once, you start with either:
- a from-folder query where you filter all files of interest or
- 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):
And for method 2 like so:
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:
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 🙂