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):
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 🙂
Neat as usual 🙂
Is the UnZip function the same as Mark White’s one, or this is other variation?
Hi Maxim, yes, it’s a variation 🙂
Thanks for sharing this! So useful. Have you had any success with extracting all the DAX from a file?
I didn’t manage to extract the DAX from closed PBIX-files yet.
If your file is open, you can DMVs via DaxStudio or some M-code in the query editor to extract DAX-definitions to extract the DAX from the model.
Another option is to save your pbix as a template (pbit) or to migrate it to SSAS in Azure: This will produce a very nice JSON-format with all your DAX.
Please let me know if you need more Infos on any of the methods mentioned.
Cheers, Imke
Pingback: SSRS APIs, M Queries, Power BI Desktop and more | Guy in a Cube
Oh, that’s great !
I cannot succeed to make Igor’s solution to work. There is a problem with binary encoding.
Would you please adapt your solution to work with the opened PBIX file ?
Thanks a lot
Hi Didier,
you can use my function to access the currently opened PBIX as well.
Just remember that you will see the last saved version then.
Cheers, Imke