How to change the dataset script in Power BI desktop

I recently on some internal email by the developers where they mentioned something, offhand, what I think is amazing :). It will allow you to add measures by copy and pasting and many more quick updates through text. Best of all it turns out there is a straightforward way to change the script in your Power BI file by hand. At this moment this is still in preview and not documented so your mileage might vary but at one point this will be fully supported, and you can try it at your own risk for now 🙂

Start by enabling the enhanced metadata format under options (this is all preview so again it might break things).

Setting this option is changing how the metadata is being stored inside the PBIX file, until now the data model format was not saved in an optimal way for scripting, now it is more aligned to how SSAS does things and we can make changes to it.

As model I just imported a simple table into Power BI desktop and created two measures.

Now to make changes to the model using text I need save it as Power BI template. This gets rid of the actual AS database that is hidden under the covers and stores the model as readable text inside the template file

Then I rename the PBIT file to zip.

and unzip it

Now the good stuff is hidden in the DataModelSchema file. I open it with Notepad++ and I can see the whole model in TMSL similar as it is for SSAS Tabular. YAY no more base 64 encoded string for those of you have dabbled with the model before :). Now there are some slight differences to the SSAS tabular TMSL but that is not yet documented (so it also can change) and you have to figure it out yourself for now, most of it is around data sources.

To make the changes make sure you use a plain text editor, I tried opening with VS code and that worked but it added some HEX symbols to the file that Power BI desktop doesn’t like. So, keep it to Notepad++ :).

Now I add a measure by just copy and pasting from another one, change the name and the DAX formula.

Now I save the file and zip up the whole folder. Rename zip back to PBIT.

Opening the PBIT file creates a new model from the TMSL and voila now we have the new measure added to the model :).

That’s it, enormously powerful and imho just the beginning of all the incredible things we will be able to do with this soon. Just one final warning, don’t call us if something breaks when doing this until this feature gets to GA :).

Happy hacking!

5 Replies to “How to change the dataset script in Power BI desktop

  1. Great post – very excited for this to go into GA. Question.
    1) Is it possible to know whether this has been enabled on a report? I would like to check whether a report has had this setting switched on.
    2) Switching this preview feature on, once you have switched on for a report is this an irreversible change. Can you switch back off?

      1. Tabular Editor *is* supported if you also check the “Allow experimental Power BI Features” in the preferences.

        But definitely caveat emptor, make a lot of backups!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.