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 🙂

Comments (5) Write a comment

  1. Thanks for sharing this! So useful. Have you had any success with extracting all the DAX from a file?

    Reply

    • 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

      Reply

  2. Pingback: SSRS APIs, M Queries, Power BI Desktop and more | Guy in a Cube

Leave a Reply