Bulk extract M code from Power BI Semantic Models
With Tabular Editor command line and… no-hands
Prelude
Exactly two years ago I wrote a blog post leveraging Tabular Editor and its scripting and command line features to automate bulk extract of Power Query (M code) from partitions of your Power BI Semantic Models.
That post was an incomplete work because there could improvements to implement.
Let’s make it better
The first improvement is to leverage authentication through service principal skipping credentials input. The following document describes clearly the setup needed:
- Serivce principal created in Azure Portal
- Microsoft Entra security group with permission to use Fabric APIs
- Workspace Access
- Semantic Model hosted on Premium Workspace
- XMLA endpoint switched on
Since we’re going to run the C# script to extract partitions over multiple workspaces and semantic models we could improve it adding a subfolder autocreation and management.
Having everything setup we can arrange a PowerShell script that loops over all Semantic Models of a Power BI tenant and launches C# script to extract M code from each of them.
That’s it, running this PowerShell you connect to Power BI using a service principal, you get a list of Semantic Models to loop over running C# to extract M partitions and saving the output in folders.
It’s important to understand Premium is needed to leverage XMLA connection but if you haven’t a Premium Capacity you can temporally assign workspaces to Premium Per User license mode, to assign workspace to Power BI Embedded or a Fabric Capacity. If you need this temporary workaround plan it carefully on PROD environment because it could cause access issues requiring different license models.
If you want to contribute to the code in this arctile you can find it in my GitHub.
Conclusions
You can use SPN to perform unattended connection to Semantic Models hosted on Fabric tenant and leveraging command line you can extract the M code behind each partition.
