In Power BI development in Microsoft Fabric, understanding and utilising source control mechanisms is crucial for efficient collaboration and version management. This blog post delves into the essential aspects of source control for Power BI. This blog also includes the recording of my session at Saudi Arabia’s Excel User Group on the 26th of August 2023. The event was organised by Microsoft MVP, Faraz Sheik, where we walked through all the topics discussed in this blog.
Understanding Source Control
At its core, source control is a system that records changes to a file or set of files over time. This lets developers recall specific versions later, ensuring efficient collaboration and error management. It’s particularly vital for development teams, allowing multiple contributors to work on the same codebase without overwriting each other’s work.
For Power BI developers, this means tracking changes made to reports, and data models that are the most crucial components of every Power BI project.
Microsoft Excel is one of the most common data sources for Power BI. We can store Excel files in various storage types. The way we get data from Excel varies depending on the storage type. In this post, I quickly show two methods to connect to an Excel file stored in SharePoint Online.
Method 1: Getting the Excel File Path from the Excel Desktop App
This method requires you to have the Excel application installed on your machine. In this method, we open the Excel files stored in SharePoint Online in the Excel Desktop App in our machine and get the file path from there.
In SharePoint Online go to the desired document library then follow these steps to make it work:
Select the Excel file
Click the Open button
Click Open in app
Opening Excel file from SharePoint Online in Excel Desktop App
This opens the Excel file in the Excel Desktop application. In the Excel follow these steps:
Click the File menu
Click Info
Click the Copy path button
Copying Excel Path from Excel Desktop App
So far we got the Excel file path. The step is to get data from the copied path in Power BI Desktop.
Open Power BI Desktop and follow these steps:
Click Get data
Click Web
Getting data from Excel stored in SharePoint Online in Power BI Desktop
Paste the path we copied from Excel in the URL text box
Delete the ?web=1 from the end of the copied path
Click OK
Modifying the path copied from Excel to get the data in Power BI Desktop
One of the most important aspects of the software development life cycle is to have control over different versions of a solution, especially in a project where there is more than one developer involved in the implementation. Just like when you normally create a project in visual studio and you commit the changes back to a source control system like GitHub or Azure DevOps, it’s advised to keep the history of different versions of your Power BI reports. What we expect from a source control solution is to keep tracking of all changes happening in the source code while developing a project. So you can easily roll back to a previous state if you like to.
The other benefit of having a source control process in place is when multiple developers are working on a single project. Every single one of them makes changes in the source code then they commit all the changes into the source control server without overwriting each others’ work.
With Power BI things are a bit different though. Power BI report files are PBIX files which are stored in binary format (well, PBIX is basically a zip file isn’t it?) which at the time of writing this post, there is no official way to enforce Power BI source control in any source control solutions like GitHub or Azure DevOps (YET).
Microsoft announced a fantastic feature last week (6/05/2020) named “Deployment Pipelines” which does exactly what we’re after, but it is currently a preview feature which is only available only to organisations with Power BI Premium. So it is out of the game for the majority of us.
Having said that, there is still a way to keep history of changes in the shape of different versions of PBIX files. This is called Version Control.
There are several ways you can enable version control over your PBIX files while developing the report. Regardless of the version control platform you need to think about having multiple environments and who can access them for doing what.
Environment
Accessible to
Description
Development
Developers
Data modellers and report writers access this environment for development purposes.
User Acceptance Test (UAT)
Developers, SMEs, Technical Leads, Power BI Admins
After the development is finished the developers deploy the solution to the UAT environment. The solution will then be tested by SMEs (Subject Matter Experts) to make sure the business requirements are met.
Pre-prod (Optional but recommended)
Technical Leads, Power BI Admins
After the solution passed all UAT testing scenarios Technical Leads or Power BI Admins will deploy it to Pre-prod for final checks to make sure all data sources are correctly pointing to production data sources and all reports and dashboards are working as expected.
Production
Technical Leads, Power BI Admins, End Users
After pre-prod checks completed Technical Leads or Power BI Admins deploy the solution to the Production environment which is then available to the end users.
Version Control Options
If your organisation does not have a Premium capacity then “Deployment Pipelines” feature is not available to you. So you need to come up with a solution though. In this section I name some Version Control options available to you
