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!
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?
This is very cool stuff. I’ve had a play with this new feature with enormous success, however, I do recommend people read this article https://www.thebiccountant.com/2020/04/13/power-bi-store-datasets-in-enhanced-metadata-format-warning/ before using this feature. Being a preview feature there are some limitations that you really need to be aware of.
Nice article.
Can’t you achieve that by using Tabular Editor?
Tabular editor is not yet supported. It will at some point but there still might be the need to do this.
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!
That’s a really cool feature!
I had a look at the Report Layout file hoping to use it to document the report and it looks promising as I can find a list of all visuals together with columns that are used on them.
However, the column name always comes with the Table/Query name in format Table.Column but the table name is not updated after I change its name in the report…
So if I started with a Table/Query named xxx and then renamed it to sth significant like Clients in the metadata I would still have columns xxx.Client Name, xxx.Client Location, etc.
Did you experience sth like this as well? Maybe there is a workaround?
Hey,
Thanks a lot for all your content, it’s great!
I was trying your method to change some partitions in my model but I keep getting the “file is corrupted or an invalid report file”. Any change I do in the “DataModelSchema”, even just adding a space on the SQL query.
Any tips for what I’ doing wrong?
Thanks
Hi Kasper,
Great post. I found it when I was looking for help with changing the names of the tables. I have a couple of reports with a live connection to a tabular model. Now I have to change the names in the tabular and I’m looking for a way to edit the code from my pbix files so I can change the table names there as wel (Like I do with SSRS). I Unzipped the pbix files and used find and replace to change the table names in all the files. after I zipped the file back to a pbix file it doesn’t word anymore.
Do you have any idea how I can make this posible?
Unfortunately that is not supported yet.
Is there a way to edit the layout file without corrupting the file?