How to create Azure DevOps Pull Requests reporting with Power BI

As a developer, I have always emphasized the importance of code quality and efficient development processes. Modern Git workflows are typically about writing code, commits, pull requests, code reviews, and merges. To gain deeper insight into these processes, I decided to create a Power BI report to track them. My goal is to identify bottlenecks, areas for improvement, and opportunities to streamline our workflow.

Pre-requisites

Before we dive into building the Power BI report, you must have Power BI Desktop of course. It is necessary to have a Personal Access Token that has sufficient access to the project repositories. You will need it to authenticate the API calls from Power BI.

Parameters

To make the report work with different settings, we will use parameters. These parameters allow you to easily apply my code to your project. Just copy the code and edit the following parameters:

_organization: The Azure DevOps organization
_project: Your project. The report will retrieve pull requests from all repositories in the project.
_top: The number of most recent pull requests you want to analyze in the report.

Build the Power Query

Fetch data from Azure DevOps

Now that you have set up your Power BI report set up with parameters and have prepared the necessary credentials, it’s time to pull data from Azure DevOps. While Power BI has a built-in Azure DevOps connector, it only provides board data. To retrieve pull request information, we will need to access the Azure DevOps REST APIs .

See the following Power BI M query:

Source = Json.Document(Web.Contents("https://dev.azure.com/"&_organization&"/"&_project&"/_apis/git/pullrequests?searchCriteria.includeLinks=true&searchCriteria.status=all&$top="&_top&"&api-version=7.1-preview.1")),

The Web.Contents function will pull data from the REST API and return a binary. The Json.Document function will grab this binary and parse it to json. After this step, you should have source as a record which has two attributes:

value: a list of all pull request records.
count: the length of the value list.

Convert to Table

Our previous step resulted in a JSON record containing the pull request data. To make this data available for further analysis, we need to convert it to a table.

#"Converted to Table" = Table.FromRecords({Source}, {"value"}),

The above query convert value to a table in Power BI. The returned table has only one column and one row like below:

value
List

To make the table usable, we need to further transform it. First, we want to explode the list to rows

#"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),

And for each row, we want to expand the record to columns. Note that we don’t necessarily need all the columns. The M query below extracts only the columns we need.

#"Expanded value1" = Table.ExpandRecordColumn(
    #"Expanded value",
    "value",
    {"repository", "pullRequestId", "codeReviewId", "status", "createdBy", "creationDate", "closedDate", "title", "description", "sourceRefName", "targetRefName", "mergeStatus", "isDraft", "mergeId", "reviewers", "labels", "url", "completionOptions", "supportsIterations", "completionQueueTime"},
    {"value.repository", "value.pullRequestId", "value.codeReviewId", "value.status", "value.createdBy", "value.creationDate", "value.closedDate", "value.title", "value.description", "value.sourceRefName", "value.targetRefName", "value.mergeStatus", "value.isDraft", "value.mergeId", "value.reviewers", "value.labels", "value.url", "value.completionOptions", "value.supportsIterations", "value.completionQueueTime"}
),

Continue expanding columns

Even though the previous steps gave us a solid starting point, some columns still have nested records full of useful data. We will perform additional expansions to access this data.

/*
value.repository, value.createdBy, value.completionOptions are records, we can expand them into columns
*/
#"Expanded value.repository" = Table.ExpandRecordColumn(#"Expanded value1", "value.repository", {"name"}, {"value.repository.name"}),
#"Expanded value.createdBy" = Table.ExpandRecordColumn(#"Expanded value.repository", "value.createdBy", {"displayName", "id", "uniqueName"}, {"value.createdBy.displayName", "value.createdBy.id", "value.createdBy.uniqueName"}),
#"Expanded value.completionOptions" = Table.ExpandRecordColumn(#"Expanded value.createdBy", "value.completionOptions", {"mergeCommitMessage", "mergeStrategy", "transitionWorkItems"}, {"value.completionOptions.mergeCommitMessage", "value.completionOptions.mergeStrategy", "value.completionOptions.transitionWorkItems"}),
/*
value.reviewers is otherwise a list of records. For each list, we will concat all displayName of each record
*/
#"Expanded value.reviewers" = Table.TransformColumns(#"Expanded value.completionOptions", {{"value.reviewers", each Combiner.CombineTextByDelimiter(", ")(List.Transform(, each [displayName]))}}),

Add details from other APIs

While the pull request endpoint provides us with a lot of useful information, it might not be enough. We often need to supplement our data with information from other Azure DevOps APIs to gain deeper insights. The process is pretty similar with what we have done so far: pulling data from API and expanding JSON objects.

Iterations

Iterations are created as a result of creating and pushing updates to a pull request. The number of iterations is equal to the number of updates made after pull requests are created. Below is the Power BI M query to get the number of iterations for each pull request:

#"Added iterations" = Table.AddColumn(#"Expanded value.reviewers", "iterations", each Json.Document(Web.Contents([value.url]&"/iterations/"))),
#"Expanded iterations" = Table.ExpandRecordColumn(#"Added iterations", "iterations", {"count"}, {"iterations.count"}),

Changes

Another good metric to track is the number of files changed in each pull request. And we need to have the changes in all iterations, not just the initial pull request. Below is the code to retrieve the data from the API and extract the required information.

#"Added iterations.changes" = Table.AddColumn(#"Expanded iterations", "iterations.changes", each Json.Document(Web.Contents([value.url]&"/iterations/"&Number.ToText([iterations.count])&"/changes?api-version=7.1-preview.1"))),
#"Expanded iterations.changes" = Table.ExpandRecordColumn(#"Added iterations.changes", "iterations.changes", {"changeEntries"}, {"iterations.changes.changeEntries"}),
#"Added iterations.changes.changeEntries.count" = Table.AddColumn(#"Expanded iterations.changes", "iterations.changes.changeEntries.count", each List.Count([iterations.changes.changeEntries])),
#"Removed iterations.changes.changeEntries" = Table.RemoveColumns(#"Added iterations.changes.changeEntries.count",{"iterations.changes.changeEntries"}),

Threads

Threads are an Azure DevOps object for managing and organizing pull request discussions. Team can discuss specific changes directly by adding one or more comments to each thread. Analyzing threads can give us many useful insights.

#"Added threads" = Table.AddColumn(#"Removed iterations.changes.changeEntries", "threads", each Json.Document(Web.Contents([value.url]&"/threads?api-version=7.1-preview.1"))),
#"Expanded threads" = Table.ExpandRecordColumn(#"Added threads", "threads", {"value"}, {"threads.value"}),

For example, we can count the comment threads. A comment thread should have the status attribute (Active, Resolved, Closed)

#"Added threads.value.commentCount" = Table.AddColumn(#"Expanded threads", "threads.value.commentCount", each List.Sum(List.Transform([threads.value], each Number.From(Record.HasFields(_, "status"))))),

Or we can get the approval or rejection information from the vote thread. A vote thread has a CodeReviewThreadType attribute with value VoteUpdate. And if the value of CodeReviewVoteResult is greater than 0, it is an approval. Otherwise, it is a rejection. The below M query get the fist approval time of a pull request.

#"Added threads.value.firstApprovalTime" = Table.AddColumn(
    #"Added threads.value.commentCount",
    "threads.value.firstApprovalTime",
    each List.Min(
        List.Transform(
            [threads.value],
            each if
                Record.HasFields(_[properties], "CodeReviewThreadType") and Record.Field(_[properties][CodeReviewThreadType], "$value") = "VoteUpdate"
                and Record.HasFields(_[properties], "CodeReviewVoteResult") and Number.FromText(Record.Field(_[properties][CodeReviewVoteResult], "$value")) > 0
            then _[publishedDate]
            else null
        )
    )
),

Full source code

You can grab the source code, paste it into the Power BI Power Query advanced editor, and customize it to suit your needs.

Full Query

Visualize insights

Now we have a rich dataset. Power BI offers a wide range of visual elements to help you uncover trends, patterns, and insights. It’s time to bring our data to life with stunning visualizations.

Conclusion

Remember, this is just the beginning. As your project evolves and your data grows, you can expand your report to include additional metrics, refine visualizations, and explore new insights. Continuous improvement is essential to maximizing the value of your data.

By creating a comprehensive pull request report, you are taking the initial step toward establishing a culture of data-driven decision-making, first within your development team, then throughout your organization.

Pre-requisites#

Parameters#

Build the Power Query#

Fetch data from Azure DevOps#

Convert to Table#

Continue expanding columns#

Add details from other APIs#

Iterations#

Changes#

Threads#

Full source code#

Visualize insights#

Conclusion#