May 27, 2023

A Beginner's Guide to Automating Power BI with the Layout File (Part 2)

Updated: Jun 7, 2023

Part 1 goes over the basics of the Layout file found within a PBIX file. It is recommended to start there. Otherwise, let's continue with automation and learn how to move the position of a visual.

Modifying visual elements

In this article, we will go over how to safely modify the visual elements in the Layout file. Doing this requires not only changing the "x", "y", "width" and "height" key-values, but also modifying the "config", "dataTransforms" and (sometimes) "query" key-values of the visualContainer elements. For reference, here is the basic structure of the entire Layout json file. I've highlighted the key values mentioned just before and, for clarity, removed key-values not applicable to this topic (...).

Main Layout JSON Structure

{
  ...,
  "sections": [
    {
      ...,
      "visualContainers": [
        {
          "x": 490.1867572156197,
          "y": 211.47707979626486,
          "z": 0,
          "width": 299.490662139219,
          "height": 299.490662139219,
          "config": "{}",
          "filters": "[]",
          "query": "{}",
          "dataTransforms": "{}"
        }
      ],
      ...
    }
  ],
  ...
}

The config property

Each of the "config", "query" and "dataTransform" values are string representations of a JSON structure. These are the meat and potatoes of your visual elements. For example, the visual type is stored here (card, bar chart, pie chart, etc.). Measures being used on the visual are stored here. The title text is stored here. The x, y, z, width, height properties are also stored here (yes, it is stored in the main visualContainers properties as well as in the "config" key). Below is the structure of the config JSON, with some key-values removed for brevity. But first, a very high level view of the structure goes like this:

name
layouts
- id
- position
singleVisual
- visualType
- projections
- prototypeQuery
- autoselectVisualType
- drillFilterOtherVisuals
- hasDefaultSort

Config JSON Structure

 {
  "name": "71e22ef0cde4efc97e8a",
  "layouts": [
    {
      "id": 0,
      "position": {
        "x": 490.1867572156197,
        "y": 211.47707979626486,
        "z": 0,
        "width": 299.490662139219,
        "height": 299.490662139219
      }
    }
  ],
  "singleVisual": {
    "visualType": "card",
    "projections": {
      "Values": [...]
    },
    "prototypeQuery": {
      "Version": 2,
      "From": [...],
      "Select": [...],
      "OrderBy": [...]
    },
    "autoSelectVisualType": True,
    "drillFilterOtherVisuals": True,
    "hasDefaultSort": True
  }
}

Some examples with Python

As you can see, there are quite a lot of things happening in the config JSON. To get the config value in Python, you need to load this into its' own json object because it is stored as a string representation of json. (If you you do not know how to get the Layout file, please refer to part 1).

# Get the first visual
firstvisual = layout['sections'][0]['visualContainers'][0]

# Get the config json
config = json.loads(firstvisual['config'])

Now you can get the properties within the config. For example, to get the visual type:

visualtype = config['singleVisual']['visualType']

To get the X position of the visual:

xposition = config['layouts'][0]['position']['x']

Move the position of a visual

As we saw above, the position of the visual element is stored twice. Once in the main properties of the visualContainer, and once again in the config properties. You need to change BOTH of these. Otherwise you risk corrupting the PBIX file. Let's say we want to move our card to x position = 10. Here's how to do that:

import json
filepath = r"Layout.json"

# Open the Layout
with open(filepath, 'r', encoding = 'utf-16 le') as f:
    layout = json.load(f)

# Get config json
firstvisual = layout['sections'][0]['visualContainers'][0]
config = json.loads(firstvisual['config'])

# Change x position in config
config['layouts'][0]['position']['x'] = 10

# Dump the config back to a string
firstvisual['config'] = json.dumps(config)

# Change the x position in the main visual container
firstvisual['x'] = 10

# Save the JSON with 'utf-16 le' encoding!
with open("Layout.json", 'w', encoding = 'utf-16 le') as outfile:
    json.dump(layout, outfile, indent = 4)

Repackage your folders into a PBIX file (remember to remove the SecurityBindings file!), and you should see the changes reflected.