If you are wondering how to automate Power BI visual elements, this guide is for you. If you want to know how to make sense of the Layout (JSON) file, this guide is for you. There isn't much documentation around (if any at all) and it can be rather confusing, so I will do my best to describe how to parse this crazy file.
What is the Layout file?
The Layout file is a JSON document (albeit without the .json extension), that contains all visual elements in your PBIX file. This includes things like pages, line charts, slicers and filters, and all properties associated with them like position, width & height, texts, colors, etc.
How to get the Layout file?
Getting the Layout file has the potential to corrupt your file. Always always always make a backup copy before doing this. Once you are ready, the first step is to rename the file extension of your Power BI file from .pbix to .zip. Then right click on the zip file and extract contents. You will now see a folder containing various files and one folder called "Report". Open this Report folder and you will see the Layout file.
1) Change from pbix to zip.
2) Extract all.
3) Open the folder.
4) Then open the 'Report Folder'. Here is the Layout file.
You can open it without an extension, but I like to add the .json extension. It's also a good practice to make a backup copy of the Layout file before modifying it.
The general structure of the Layout file
A general, high-level view of the Layout file follows the pattern of:
PBI properties
Sections (these are your pages/tabs)
Page properties
Visual containers (these are your cards, bar charts, ect.)
Visual properties (positions, dimensions, etc.)
More detailed look at the Layout structure
A Layout file that has only one card visual looks like the following. I've removed the values for the config, query, and dataTransforms keys because they tend to be lengthy, but hopefully you get the idea.
{
"id": 0,
"resourcePackages": [
{
"resourcePackage": {
"name": "SharedResources",
"type": 2,
"items": [
{
"type": 202,
"path": "BaseThemes/CY23SU04.json",
"name": "CY23SU04"
}
],
"disabled": false
}
}
],
"sections": [
{
"id": 0,
"name": "ReportSection",
"displayName": "Page 1",
"filters": "[]",
"ordinal": 0,
"visualContainers": [
{
"x": 490.1867572156197,
"y": 211.47707979626486,
"z": 0,
"width": 299.490662139219,
"height": 299.490662139219,
"config": "{}",
"filters": "[]",
"query": "{}",
"dataTransforms": "{}"
}
],
"config": "{}",
"displayOption": 1,
"width": 1280,
"height": 720
}
],
"config": "{}",
"layoutOptimization": 0
}Modifying the file is straightforward and complex at the same time. Opening the Layout file with a program like Notepad++ is generally recommended instead of your machine's default reader (like Notepad). This is because Notepad ads an invisible bite of data whenever modifying the file. This ends up corrupting the file. Notepad++ does not do this and therefore a safer option. You can also programmatically modify the file with your choice of language (like Python, for example).
Reading the Layout file with Python
If you open the Layout file with Notepad++, you'll notice a messy looking JSON file. You also risk the potential of accidentally changing some part of the file by mistake. To remedy both these situations, using Python to read the file is a great solution. You only need to import the json library.
import json
filepath = r"Layout.json"
with open(filepath, "r", encoding = 'utf-16 le') as f:
data = json.load(f)IMPORTANT: You need set the encoding to 'utf-16 le'. Otherwise, loading the json object will fail.
Some simple examples:
To get the page name of the first page:
pagename = data['sections'][0]['displayName']To get the x position of the first visual of the first page:
xposition = data['sections'][0]['visualContainers'][0]['x']Changing the name of a tab
This is as simple as changing the value of the key-value pair of your json dictionary.
data['sections'][0]['displayName'] = 'New Page Name'Then save the json by dumping it.
with open("Layout.json", 'w', encoding = 'utf-16 le') as outfile:
json.dump(data, outfile, indent = 4)Rebuilding the PBIX file
1) Once you've modified the Layout file, you need to delete the SecurityBindings file.
2) Then compress the rest of the contents into a zip file.
3) Finally, change the .zip extension to .pbix.
If successful, Power BI should open up without errors, and the first tab name should show the change.
Up next...
Modifying visual elements involves more steps than simply changing the values you see above (hint; it involves the "config", "dataTransforms" and "query" key value pairs). In the next article, I will go into detail of the visualContainers and how to modify the individual visual elements without causing file corruption.
Comments