Forum Discussion
Programmatically set data colors by unzipping .pbix and modifying Layout.file
Hi there, big thanks to everyone in the community for all the help so far.
At the moment I am trying to tackle the problem of setting visual fields of my reports for example data colors in visualizations programmatically.
When creating 20 page reports with as many as 40+ visualisations that currently need every single data color of every visualisation defined manually by hex code (as seen below) it feels like I am wasting a lot of time.
Manually this becomes a ridiculously arduous process. Setting it programmatically would take a few minutes for each report as each visualisation uses the same colors for the common fields (as seen below).
I have posted on this before and made a suggestion in the ideas forum. But I am now testing an alternative approach.
I have noticed that .pbix project files can be renamed to .zip extension and unzipped revealing the Layout file containing all the visual properties I have been looking for. From here I can easily set all of my data colors with a few 'Find & replace' queries found in any common text editor.
The only problem is when re-zipping the file back to its original structure and renaming the extension back to .pbix
When opening the modified file I am confronted by the following error message:
I understand the security concerns from microsoft on this sort of workaround but there really must be a way to do it safely.
Any responses are appreciated.
If I open the pbix file with 7-Zip, open the Layout file with Notepad++, change the color and save the Layout file to local drive then replace the original one in 7-Zip, I will got the same error message as you when open the changed pbix file.
But if I copy this changed pbix file to another machine which installs the same version of Power BI Desktop, the file can be opened properly and the color has changed properly.
So you can try to modify the layout file in one machine, but do not open this file with the same machine. I’m not sure why but you can have a try. For how to use 7-Zip and Notepad++, please refer to this article.
Best Regards,
Herbert
12 Replies
If I open the pbix file with 7-Zip, open the Layout file with Notepad++, change the color and save the Layout file to local drive then replace the original one in 7-Zip, I will got the same error message as you when open the changed pbix file.
But if I copy this changed pbix file to another machine which installs the same version of Power BI Desktop, the file can be opened properly and the color has changed properly.
So you can try to modify the layout file in one machine, but do not open this file with the same machine. I’m not sure why but you can have a try. For how to use 7-Zip and Notepad++, please refer to this article.
Best Regards,
Herbert
With regard to copying the file to another machine to work arond the corrupted file message, another way to get around the message is to open the file in 7-zip and delete the SecurityBindings file. There must be a checksum associated with the current machine in the security bindings file. After deleting the file, Power BI opens it without complaining, and then modifying in Power BI will restore the bindings.
Also be sure to use Notepad++ as your editor for 7-zip...or at least be sure NOT to use Windows Notepad. Windows Notepad adds an invisible byte order marker at the start of the document (0xfffe for little endian text files) which also triggers a corruption message in Power BI.
I got the same error message about corrupted report !
If you are wanting to do this to keep consistency of legend colours between visuals then there is much easier, and quicker, process which can be followed here: https://community.powerbi.com/t5/Desktop/Color-Linking/td-p/221636
