how to load files from azure devops repository straight into power bi.
introduction.
Automating the documentation of code, i.e. with markdown, has become increasingly popular. If the code of your project is written in a format consumable by Power BI, why not utilising this and creating an automated documentation in the shape of a Power BI report? For instance, Azure Data Factory is built upon JSON files. You could pull that information from the repository into Power BI to build a meta report on top of it. The great thing: Anytime your data factory changes, your documentation is updated, too. Interestingly enough, Power BI does provide an Azure DevOps connector. However, it lets you only consume the data from Azure DevOps’ Item Board. Luckily, there are ways to connect to the Azure DevOps repo as well. This post provides a walkthrough on how to achieve this.
.
prerequisites.
1. An Azure DevOps project with a repository and a JSON file
2. Power BI Desktop
.
plan of action.
1. What is our starting point?
2. Connect to Azure DevOps repository
.
1. What is our starting point?
All we need is an Azure DevOps project with a repository containing a JSON file:
.
2. Connect to Azure DevOps repository
Open Power BI Desktop and click on Blank query under Get data.
.
Then, paste the following code snippet into the query window and click Edit Credentials:
= VSTS.AccountContents("https://dev.azure.com/[organization]/[project]/_apis/git/repositories/[repository]/items?path=[filepathincludingfile]&download=true&api-version=5.0").
Sign into Azure DevOps and click connect.
.
A file should pop up now. Right-click it and choose the appropriate file format. In our case, it is JSON:
.
Now, we need to convert the outcome into a table:
.
Et voilà: The table fetched from the JSON file which is laying in the Azure DevOps repo:
.
With the data being loaded into Power Query, you can now do all the transformations needed. In our case, we would like to pivot the table:
.
And finally, the table as desired:
.
end.
Obviously, data originating from such files can be quite messy. This is no real surprise, since they are created to build a programmatic solution and not tables to be consumed by a third party visualisation tool. Still, with a few tricks in Power Query and DAX, you might be able to use such files to create your very own solution documentation in Power BI. As an example, below a report which was built on top of Azure Data Factory JSON files that were created to customize triggers for different environments. In the next blog post, I’ll show you how to built such an Azure Infrastructure lineage report directly from files laying in an Azure DevOps repository.
