Enhancing Your DevOps Reporting in Power BI

My role in Product Management requires me to use DevOps a fair amount, lets just say I always have it open in one way or another. And whilst there are a number of tools out there these days to manage backlogs, roadmaps and everything else in between, it turns out DevOps and PowerBI were all I needed. This blog post focuses on enhancing your DevOps reporting in Power BI.

The challenge

The main connector in PowerBI to pull data from DevOps is perfect for getting started but it only pulls a basic set of data.

The default connector for example if we take the Work Items – All history endpoint gives you the following:

• Area Path
• Assigned To
• Iteration Path
• Work Item Id
• Title
• State
• Work Item Type
• Data
• is Current

And this is generally fine if you want a quick view. But if you need to review priorities, further information under those user stories. All those useful data points that can make a much more effective dashboard, we need to go further.

Luckily we can expand this data out using APIs by simply using this first initial connection as a base.

The function

In PowerBI if we add the following as a function thats queries the DevOps API directly, meaning we can then use the Work Item Id to get those extra details

In the below Ive set DEVOPSORG and DEVOPSPROJECT as parameters matching up with my Organisation name and Project Name.

https://github.com/AetherAdv/powerbi_product_devopsfunction

let
    FetchWorkItemSummary = (workItemId as text) as table =>
    let
        // Compose the request URL with API version
        EndpointUrl = "https://dev.azure.com/DEVOPSORG/DEVOPSPROJECT/_apis/wit/workitems/" & workItemId & "?api-version=7.1-preview.3",
        
        // Attempt to retrieve JSON data from Azure DevOps
        RawResponse = try Json.Document(VSTS.Contents(EndpointUrl)) otherwise error "No details for ID " & workItemId,
        
        // Verify expected structure
        HasFieldsSection = if Record.HasFields(RawResponse, "fields") then RawResponse[fields] else error "Missing 'fields' data for item " & workItemId,
        
        // Pull out specific fields
        TaskPriority = Record.FieldOrDefault(HasFieldsSection, "Microsoft.VSTS.Common.Priority", null),
        TaskDescription = Record.FieldOrDefault(HasFieldsSection, "System.Description", null),

        // Convert to a table format
        Output = Table.FromRecords({
            [Priority = TaskPriority, Description = TaskDescription]
        })
    in
        Output
in
    FetchWorkItemSummary

As part of this code Ive then collected the Priority and the Description, so instantly gaining more useful information for reporting.

There are of course more fields that we can then collect if needed. Use this table below to identify the field reference and drop into the function.

Field Name	Field Reference	Notes
Title	System.Title	Short name of the work item
State	System.State	Current workflow state (e.g. New, Active, Closed)
Work Item Type	System.WorkItemType	Type (e.g., Bug, Task, Epic)
Created By	System.CreatedBy	User who created the work item
Created Date	System.CreatedDate	When the item was created
Changed By	System.ChangedBy	Last user who changed it
Changed Date	System.ChangedDate	When the item was last updated
Assigned To	System.AssignedTo	Current assignee
Tags	System.Tags	Comma-separated string of tags
Area Path	System.AreaPath	Hierarchical area assignment
Iteration Path	System.IterationPath	Sprint or iteration it belongs to
Effort / Story Points	Microsoft.VSTS.Scheduling.StoryPoints	Often used for estimation
Remaining Work	Microsoft.VSTS.Scheduling.RemainingWork	Typically used in Tasks
Priority	Microsoft.VSTS.Common.Priority	Used for sorting/backlog
Severity	Microsoft.VSTS.Common.Severity	Often used in bugs
Description	System.Description	Full rich-text description

Why?

This data now forms the basis of dashboards I and other stakeholders rely on when reviewing the backlog, keeping up to date on the road map and much more. Enhancing your DevOps reporting in Power BI means we can get the extra fields we need for stronger reporting capability.

Coming Next

Next up I will show some of this data in some of the dashboards I use today, how I’m continuously improving them to support better decision making and how you can also looking to use them in your own environment.