Bulk extract M code from Power BI datasets
Tabular Editor and nothing else
Prelude
Your company deeply adopted Power BI and now you have many Power BI datasets published on Power BI service. Each of these datasets can have multiple tables and you want to analyze how data are extracted and prepared from the sources. In other words you want to verify all the query steps (M code) performed in each step.
Collecting and sniffing all .pbix to extract all the quries manually could be not an option as soon as your environment becomes complex.
Automating the process could save a lot of time.
How to
In a Power BI dataset (or model), each table has partitions behind the scenes, and M code to populate those partitions is saved into the dataset itself.
Power BI Desktop it’s not the only tool able to read the Power Query behind a table in a model, you can also use external tools like Tabular Editor.
Tabular Editor can do this task faster than Power BI Desktop but it stills a manual process.
Tabular Editor gives the possibility to execute C# based scripts over connected models:
- Advanced Scripting in Tabular Editor 2
- C# Script in Tabular Editor 3
Explaining the power and the dozens usecases of this feature is out of scope of this post, but we’re going to leverage it for our needs.
The idea is to build a C# script that loops over all the tables in a model extracting the code of each partition.
The above code (or something adjusted to better suit your needs) will dump all the M code into separate files in the specified directory.
So we have a super fast extractor that can export all the queries from a model attached to Tabular Editor.
Wouldn’t be nice not to have to open TE to run that script?
Guess what? TE has a command line interface that we can leverage to perform the following steps:
- Open Tabular Editor from command line (or PowerShell)
- Attach TE to a model
- Run a script over the model
For the sake of simplicity our model has already been saved as .bim file on the file system and what we need to do is to run the following .bat.
NB: TE3 currently doesn’t support Command Line syntax yet, and the development team is planning to release a dedicated exe for this purpose. Until then use TE2 to leverage Command Line interface.
The result it’s not bad at all but we can do even more (Premium Level). Tabular Editor can connect to Analysis Services Tabular instances, both on-premises and on-cloud and obviously can connect to Power BI datasets published on the service if XMLA endpoint is open.
Considering you have a Premium Capacity or a Premium Per User workspace, you can programmatically start Tabular Editor on your machine to connect to a published dataset and download it’s queries locally, without moving the dataset somewherelse or using the original .pbix.
TIP: If you don’t have a Premium workspace, you could temporally set your workspace as Premium per user and switch it back to Pro as soon as the extraction is completed. Be aware that Pro users will be prevented to access while workspace is setup as PPU.
Having the Workspace Connection and valid credentials, you can setup the following script accordingly specifying the model to sniff.
Can we furthermore improve this final? Surely we can:
- leverage SPN connection in order not to be prompted for authentication
- implement a loop over multiple datasets to extract queries from multiple dataset in one single shot
Maybe something for a future post :-).
As soon as your queries are extracted you can do M(agic) transformations to collect relevant insights out of them.
Conclusions
Leveraging Tabular Editor and its scripting and command line features you can automate bulk Power Query (M code) from partitions of your Power BI models, even if they’ve already been published on the service, without moving them.
