Power BI: How I Started Using Python To Automate Tasks
As many data enthousiasts, I use Power BI on a daily basis to build dashboards and visualize my data. However, while building and improving a complex dashboard, I constantly add and delete widgets using different fields and corresponding tables. After numerous iterations the data structure starts to look like a real mess, resulting a cluttered list of many tables and numerous columns or fields that I used at one point to build my dashboard. However, in the end it would be nice to get an overview of all the fields I am actually using in my final dashboard. Unfortunately, the only way to achieve it this in Power BI, is going trough every widget and manually take a look and write down the fields that have been used.
As programmers, we try to avoid manual work by any means necessary, so I did some digging to see if some code could replace this repetitive work. Now ofcourse, I wouldn’t write this article if I didn’t come up with a solution. It turned out it was rather simple to accomplish this.
Deconstructing PBIX files
The first step in messing with Power BI without actually using the software is digging in the .PBIX file. Using free compression software like WinRAR you can unzip any .PBIX. file. This results in a structure of different files and directories offering a realm of possibilities for analysis and possibly manipulation.
When going trough the decompressed file you can find a folder ‘Report’ containing a ‘layout’ file. This file contains all the information concerning the visual structure of your dashboard:
- X/Y coordinates and size of every widgets
- Names of used fields
- All settings and parameters
- Titles
- … and much more
The file consists of a string which is a sort of combination of JSON, lists and dictionaries. Using Python, I planned to generate a summary of the data fields and widgets actually used.
Building a parser
I was hoping a simple JSON parser would to the trick, but unfortunately within the JSON other structures are nested, making the code a bit longer than I hoped for but overall still pretty short. After a little bit off puzzling and building in scenario’s for exceptions I got some pretty decent results returning a list of tables and corresponding fields for each widget.
Using Pandas I summarized this information to a dataframe, removing all duplicate information (for example, two widgets using the same data). The result is clean dataframe containing all the data I used in my dashboard looking like this:
Online version
Ok, so now every works fine in a Jupyter Notebook by copy pasting the string from the layout file and letting my function do the work. Working fine, but not in a very user friendly way, so on the next step: converting the function into a useable tool.
Not wanting to abandon my loyal companion named Python, I chose Flask to build a web app. As a I am not a front end developper I went for a minimalistic approach not paying too much attention to the visual aspect.
Basically, it’s just a form taking the string as input, running the function, returning the dataframe, converting the dataframe to an HTML table and finally showing it to the user. The tool is free and contains no adds. It might be a little slow from to time as it is hosted on a Heroku webserver.
What’s next?
Alhtough I am happy with automating this task, it still only feels like a start. Expanding the script and exploring other files within the decompressed .PBIX file could open many new doors. For example, I briefly experimented with minor manipulations in to the file, compressing again and reopening it through Power BI again, which seemed to work. This means it should be possible to use Python scripts to edit dashboards.
Not sure yet where I will go next from here, but some of the options could be:
- tracking changes
- auto-generating reports
- creating more extensive reports
- …?
If you have any other ideas or feedback, feel free to leave them in the comments.
For those who want to use the tool, you can find it here.
About the author: My name is Bruno and I work as a data scientist with Dashmote, an AI technology scale-up headquartered in The Netherlands. Our goal is bridging the gap between images and data thanks to AI-based solutions.
Check out my other work on https://www.zhongtron.me.
