A solution to track changes (source control) and to enable multiple users to work simultaneously in Power BI (PBI) report.
Tracking changes in PBI has always been my biggest question since I started to use it.
If you have ever searched Google for “how to track changes in Power BI” or “source control in Power BI”, you should see a lot of results about this topic. Some of them are open questions that are still under discussion. Some of them suggest approaches that actually work but are too complicated for beginners.
In this article, I would suggest a simple solution to tackle two common pain points for data analysts when working with PBI:
- How to efficiently store report versions and compare changes?
- How can multiple persons work seperately on a single Power BI report and then combine outputs together?
Setup
To start with, we must install the necessary applications or packages::
- Power BI Desktop(encourage to install recently new versions)
- Git / Github account
pbi-tools
— a Dev-Ops tool for PBI. Installation guidelines for this tool are available at https://pbi.tools/tutorials/getting-started-cli.html . It’s actually not an installer, you just need to download, unzip the folder, add a path to that folder and execute the file.
How to efficiently store report versions and compare changes?
For this project, I use US Population Estimates.csv
dataset to create a PBI report and illustrate the idea. The dataset can be downloaded from data.world.
Firstly, I create a PBIT file. In contrast to PBIX, PBIT files only preserve the report format and exclude the underlying data, user needs to refresh the datasource each time they open the file. The template, not the datasource, is what we want to monitor.
The initial version of the report is as below:
To save the first version, we run the following commands:
pbi-tools extract "us-population-report.pbit"
➡️ This will extract all metadata of the report and store them in a folder.
- Push the first commit to Github:
git add .
git commit -m "pbi - first version - add sheet population in 2023"
git push
When we submit code to Git, it is strongly advised that we avoid including the .pbit, .pbix and data folder. You can add *.pbit, *.pbix, and /data to .gitignore.
- For instance, I understand that the title of the page needs to be changed to “U.S. Population Estimate in 2023” in order to better reflect the analysis purpose. After making changes, we run the same sequence of commands as above and push a second commit to Github. The changes are reflected as below:
This is cool! Everytime a person pushes some new commits, we will see all changes on Github.
How can two persons work seperately on a single Power BI report and then combine outputs together?
Assume I have a stable version of PBI report from dev
branch, I want to give 2 data analysts tasks to independently generate 2 additional analysis tabs. I will then integrate these versions into the dev
branch. The following diagram represents the Git workflow:
- feature-linechart: The first analyst adds a new sheet that shows the population estimates from 2016 to 2060.
- feature-datatable: The second analyst adds a new sheet to display detailed dataset that can be filtered by Sex, Race and Origin.
Once tasks are done and PRs (Pull Request) are created, we can merge them together to dev
branch.
Notes: Everytime a
pbi-tools extract
command is run, it generates a unique report ID and a new updated timestamp. We should expect some minor merge conflicts. If the works of 2 analysts are not overlapped, it should be easy to solve these conflicts.
Once the merge process finishes:
- From local repository,
git pull
to pull the latest changes fromdev
branch. - Run
pbi-tools compile “us-population-report" -overwrite True -format PBIT
➡️ This command reads the metadata stored in folder and generates a report. - Now, we have a new report that includes 2 new tabs (Linechart — Population Estimate 2016–2060 and Data Table).
Thanks to pbi-tools
and git
, we can make this possible. Please give me as many claps as you can and follow me to get more tips in future 👍.