PowerBI Resizing report

This short post is just an example of why it is really powerfull to have your files in readable and processable files on disk (and in source control).

We had a PowerBI report that has grown over several years with lots of visualizations and explanations for the readers. Unfortunately, the report was initially created with a way too big page size so exporting to pdf and printing was really an issue. While we could workaround for most of the text blocks by using a significant larger font, most of the the visuals became almost impossible to read as the texts was really really small.

Luckily, Microsoft have made a lot of effort to save reports in a text format, so when faced with this challenge I could process the report and automatically resize the pages and the visualizations and move them into proper places on the new resized pages. With the .pbip project and the new PBIR format the PowerBI report is saved as a folder with multiple json files that can be read and understood even though it is of course a bit technical and with a lot of details. What is more important is that it is fully supported.

Unlike PBIR-Legacy (report.json), PBIR is a publicly documented format that supports modifications from non-Power BI applications.

How to activate

We need to activate .pbip and PBIR preview features in options.

The structure

Ok so when document is saved in PBIP format, a whole folder structure is generated.

There is a <name>.Report folder with two folders StatisResources and definition. In definition there are apges with a folder for each page and in there are a folder with visuals and a folder for each visual.

Both pages and visuals have a (strange) autogenerated name. You can actually rename it, as long as you rename both the folder and the name property in the json file (page.json and visual.json). Specifically for pages, there are also in root a pages.json file which has the ids of the pages, as it is specifying the order of the pages.

When there is a semantic model (this report is refering another model) there will also be a <name>.SemanticModel folder. In here will be definitions/tables folder with a .tmdl for each table. This file is a text file with indentation and describes each column with its properties and also a partition section with the “Power Query M Formula Language” (formerly known as “M”) query used for loading data.

Resizing script

When investigating the pages and creating new page in proper A4 size, it turned out that we had the pages exactly double size of what it should be.

This made the fixing much simpler as now we can just half the values. Sometimes you are lucky… With this small script all pages are resized and the visualizations are resized and moved.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

$pageFolders = Get-ChildItem (Join-Path $PSScriptRoot "definition" "pages") -Directory
foreach($pageFolder in $pageFolders) {
    # Set page size
    $page = Get-Content (Join-Path $pageFolder "page.json" -Resolve) |ConvertFrom-Json
    $page | Select-Object -Property name, height, width
    $page.height = $page.height / 2
    $page.width = $page.width / 2
    Set-Content -Path (Join-Path $pageFolder "page.json" -Resolve) -Value ($page | ConvertTo-Json -Depth 100)

    # Handle visuals
    $visuals = Get-ChildItem -Path $pageFolder -Recurse | Where-Object { $_.Name -eq "visual.json" }
    foreach($visualFile in $visuals) {
        $content = Get-Content $visualFile | ConvertFrom-Json
        $content.position.x = $content.position.x / 2
        $content.position.y = $content.position.y / 2
        $content.position.width = $content.position.width / 2
        $content.position.height = $content.position.height / 2
        Set-Content -Path $visualFile -Value ($content | ConvertTo-Json -Depth 100)
    }
}

After running it, I can open the report to verify and adjust some of the font sizes (just half the value didn’t really work, so no automation for this part), and commit the hundreds of files that was changed - luckily not manually.

Considerations

According to the documentation, the new PBIR format cannot be used with the (Fabric based) automatic deployment pipelines. We don’t have access to this (in this particular organization) due to licenses available, so it was not something that we had to take care of. We update the reports online by saving the report to the old .pbix file and upload it manually.