Querying the Azure DevOps Work Items API directly from Power BI

As Partners for Power BI, endjin is doing more and more interesting things with data and visualisations every week.

Recently we were asked to develop some custom visualisations based on work item KPI data from inside Azure DevOps (ADO), which required us to get to grips with the underlying ADO REST API.

This post walks through that process, explaining how to connect to and query ADO from within Power BI, use Power BI functions to create re-usable sub-queries that can be composed into larger queries and will hopefully leave you with a solid base for developing your own custom VSTS-based Power BI charts and visualisations.

Before we go any further, I should highlight that if you're just starting out with surfacing your ALM data inside Power BI, there's already an number of pre-packaged content packs including one for ADO.

This provides a number of incredibly useful visualisations over your Git commits and work items. However, although new reports can be built on top of the underlying data sets that a content pack provides, if there's data that you need that isn't exposed in the content pack, there's not a lot you can do - the content packs are by definition non editable, so what goes on under the covers is a black box.

This was exactly the situation we were in, the data that we needed to support the custom KPIs was not retrieved by the content pack, so we had no choice but to build custom queries ourselves. Another reason for going down this route is that the ADO content pack is only available in the online version of Power BI - if you want to develop locally with Power BI Desktop, then you'd also be restricted to this approach. Either way, this post will show you that creating custom queries over the ADO REST API is not only possible, but straightforward, and it will give you a totally flexible solution for retrieving any data that is exposed through the ADO API.

Overview

The solution described here relies on calling various methods in the ADO REST API to retrieve detailed work item data, however the same principles could be applied to any of the entities retrievable from the API. Due to the endpoints and operations that are exposed, the following set of steps are followed to surface the data:

1. Execute a ADO stored query using the work item query API to retrieve a list of work item IDs
2. Split the list of work item IDs into groups of 200, which is the maximum batch size that the work items API supports
3. Call the work items API for each list of 200 work item IDs to get the work item field data
4. Combine into a single dataset for use in charts and visualisations

Set up the Azure DevOps query

The ADO API does include an endpoint that allows you to execute "work item query language" as part of the request, meaning dynamic queries could be composed and executed directly from your client application. However, Power BI doesn't support this - it requires POSTing to an authenticated URI with body parameters, which it deems insecure, as this scenario in REST would typically be used to update or delete data.

However, all is not lost, as the work item query endpoint also allows a stored query (i.e. an existing saved ADO query) to be executed by specifying its ID in the request.

So, the first step is to create, or identify an existing query that you wish to use to retrieve the set of work item IDs that you care about. This query will be the backbone of everything else that happens in Power BI - we'll be hard coding the query ID into the Power BI query - so all subsequent steps will work on that set of work item IDs. If you intend to create charts or visualisations across a number of iterations, or projects, or areas, then it would be advisable to make this query as wide as possible - grab as much data as you care about in this query so you can then apply the filtering dynamically inside Power BI.

However, bear in mind that some ADO endpoints will only accept a single ID at once, rather than a batch request - for example, if you wanted to retrieve the update history for each work item - and these would require a subsequent request for every ID returned from the original query, which will have a direct impact on performance. The key is finding the right balance of flexibility (by bringing back lots of data into Power BI for filtering), and performance (by only bringing back the data you actually need to limit the number of API calls required) and this will totally depend on your own specific circumstances and data sets.

Creating dynamic Azure DevOps queries

An alternative way to approach the ADO query could be to use the dynamic macros inside the ADO query so that the query itself always returns up to date data - e.g. filtering with Iteration Path = @CurrentIteration means that as you move into new iterations, the query will always return current work item data. Running the ADO query inside ADO will show you the data set that you'll have inside Power BI to use. Once you've been through the following steps and wired up Power BI to this query, any subsequent changes to this query will automatically affect the data set retrieved in Power BI when it is refreshed.

Get the Azure DevOps query ID

Whether you create a new query, or use an existing one, you'll need to obtain the GUID for this query so that it can be used from inside the Power BI query. This can be retrieved from the ADO API by executing the following request in a browser, or HTTP Client (e.g. Postman):

https://[instance-name].visualstudio.com/DefaultCollection/[project-name]/_apis/wit/queries/[path-to-query]/

Where:

• [instance-name] is the ADO instance name
• [project-name] is the ADO project name
• [path-to-query] is the path to the query e.g. "Shared Queries/Current Sprint/Product Backlog

e.g. https://endjin.visualstudio.com/DefaultCollection/PowerBI/_apis/wit/queries/Shared Queries/Current Sprint/Product Backlog/

The response will look something like this, and the value you're interested in is the first "id" value - in this example it is AC9C8A69-A593-4E7F-BB63-1B93930FEEAC. Make a note of this GUID as you'll need it in the next step.

{
	"id":"AC9C8A69-A593-4E7F-BB63-1B93930FEEAC",
	"name":"Product Backlog",
	"path":"Shared Queries/Current Sprint/Product Backlog",
	"createdDate":"2016-06-07T10:30:42.503Z",
	"lastModifiedBy":{
		"id":"685F2746-21BD-46EF-94F4-A2469B2E3CF1",
		"displayName":"James Broome"
	},
	"lastModifiedDate":"2016-06-07T10:36:00.187Z",
	"isPublic":true,
	"_links":{
			"self":{
				"href":"https://endjin.visualstudio.com/DefaultCollection/1D73904C-5EFB-4A43-8EB0-D0FBA9A42C39/_apis/wit/queries/AC9C8A69-A593-4E7F-BB63-1B93930FEEAC"
			},
			"html":{
				"href":"https://endjin.visualstudio.com/web/qr.aspx?pguid=1D73904C-5EFB-4A43-8EB0-D0FBA9A42C39&qid=AC9C8A69-A593-4E7F-BB63-1B93930FEEAC"
			},
			"parent":{
				"href":"https://endjin.visualstudio.com/DefaultCollection/1D73904C-5EFB-4A43-8EB0-D0FBA9A42C39/_apis/wit/queries/1D73904C-5EFB-4A43-8EB0-D0FBA9A42C39"
			},
			"wiql":{
				"href":"https://endjin.visualstudio.com/DefaultCollection/1D73904C-5EFB-4A43-8EB0-D0FBA9A42C39/_apis/wit/wiql/AC9C8A69-A593-4E7F-BB63-1B93930FEEAC"
			}
		},
		"url":"https://endjin.visualstudio.com/DefaultCollection/1D73904C-5EFB-4A43-8EB0-D0FBA9A42C39/_apis/wit/queries/AC9C8A69-A593-4E7F-BB63-1B93930FEEAC"
}

Execute the Azure DevOps query from a custom Power BI query

Now that you have a query inside ADO that returns a list of work item IDs, you can create a Power BI query to execute it. Open Power BI Desktop and add a new custom query through the Get Data | Blank Query option. Name the query "GetWorkItemIds" - (this is important as the subsequent code examples will refer to this query by name) and open the Advanced Editor to edit the contents of the query.

Copy and paste the following query code into the Advanced Editor (replacing the default skeleton query code). It creates a Power BI function - i.e. a reusable query that can be called from inside other queries - which calls the ADO work item query API, specifying the query ID and returning the list of work item IDs.

let
GetWorkItemIds = () =>
let
    Source = Json.Document(Web.Contents("https://[instance-name].visualstudio.com/DefaultCollection/[project-name]/_apis/wit/wiql/[query-guid]")),
    workItems = Source[workItems],
    #"Converted to Table" = Table.FromList(workItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expand Ids" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id"}, {"Work Item Id"})
in
    #"Expand Ids"
in
    GetWorkItemIds

You'll need to update the following values in order to execute the function:

• [instance-name] is the ADO instance name
• [project-name] is the ADO project name
• [query-guid] the GUID value of the ADO saved query in the previous step

Close the Advanced Editor to apply the changes, and the query should show as a function inside Power BI with an "Invoke" button - a bit like this:

Clicking the Invoke button will do just that, and, if all is well you should see a list returned of the IDs of the same work items returned by the ADO query.

Note - "invoking" a Power BI function inside the query editor adds an extra Applied Step to the query. This causes problems when other queries depend on it, so make sure you remove the Applied Step afterwards each time by clicking the X next to the Invoked FunctionGetWorkItemIds step.

Authenticating with the ADO service

When the function is invoked, you will need to authenticate against the ADO service, using either Basic, Organisational Account, or oAuth authentication, depending on what is supported in your ADO service.

Two Factor authentication is not supported in Power BI yet for this type of data source, but Basic authentication can be used by setting up Alternative Authentication Credentials inside ADO Security Profile and specifying the username and password.

Which ever authenticated method you choose to use, make sure that you set the authentication credentials to apply at the top level domain level, so that all other ADO queries against the same API will be automatically authenticated.

Use the list of IDs to get the work item details

Now that you're retrieving the list of work item IDs, the following steps use that list to call additional ADO APIs to retrieve specific pieces of data about the work item. In this scenario, we're retrieving all the primary work item fields available, but deeper API calls can be made to retrieve collections of child data about a specific work item - e.g. all the history records as the work item has changed over time, or all the linked work items. In those cases, you'd be calling the API once per work item ID but if you're querying the work items API, you have the ability to request the details for a list of up to 200 work item IDs at a time.

Paging

The next query to create is another function that handles this requirement to page the list of work items IDs into groups of 200. Use Get Data | Blank Query to create a new query and open the Advanced Editor. Copy and paste the following query code into the Advanced Editor (replacing the default skeleton query code) and name the function "GetWorkItemIdsPages" - again, the name is important as the functions are referenced by name from other queries.

let
    GetWorkItemsPages = (ids as list) =>
let
    Pages = List.Numbers(0, Number.RoundUp(List.Count(ids) /200)),
    #"Convert to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Split List to CSV" = Table.AddColumn(#"Convert to Table", "Custom", each Text.Combine(List.Transform(List.Range(ids, [Column1]*200, 200), each Text.From(_)), ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Split List to CSV",{"Column1"}),
    Custom = #"Removed Columns"[Custom]
in
    Custom
in
    GetWorkItemsPages

Calling work items API

Once you have the paging function in place, you can add a third function to actually call into the work items API to retrieve the details - for every work item ID in a list, batched into pages of 200. Use Get Data | Blank Query again and open the Advanced Editor. Copy and paste the following query code into the Advanced Editor (replacing the default skeleton query code) and name the function "GetWorkItems" - again, the name is important as the functions are referenced by name from other queries.

let
    GetWorkItems = (ids as list) =>
let
    Pages = GetWorkItemsPages(ids),
    #"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Results" = Table.AddColumn(#"Converted to Table", "WorkItems", each Json.Document(Web.Contents(Text.Replace("https://[instance-name].visualstudio.com/DefaultCollection/_apis/wit/workItems?ids={ids}", "{ids}", [Column1]))))
in
    #"Results"
in
    GetWorkItems

You'll need to update the following values in order to execute the function:

• [instance-name] is the ADO instance name

Combining the queries to return the work item data

Finally, you now need to add the last query that ties everything together - using the GetWorkItemIds function to retrieve the master list of IDs, passing that into GetWorkItems, which subsequently uses GetWorkItemsIdsPages to slice up the list of IDs into pages of 200 before calling into the work items API to retrieve the work item data fields.

Use Get Data | Blank Query again and open the Advanced Editor. Copy and paste the following query code into the Advanced Editor (replacing the default skeleton query code) and name the query "WorkItems". This query isn't created as a function - it's a regular Power BI query that will return and display data that can be used in charts and visualisations in the reports view.

let
    Source = GetWorkItemIds(),
    #"Changed Type To Text" = Table.TransformColumnTypes(Source,{{"Work Item Id", type text}}),
    #"Call Work Items" = GetWorkItems(Table.ToList(#"Changed Type To Text")),
    #"Expand Data" = #"Call Work Items"{0}[WorkItems],
    #"Drill Down List" = #"Expand Data"[value],
    #"Converted to Table" = Table.FromList(#"Drill Down List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expand Record" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields"}, {"Work Item Id", "fields"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Expand Record", "fields", {"System.AreaPath", "System.TeamProject", "System.IterationPath", "System.WorkItemType", "System.State", "System.Reason", "System.CreatedDate", "System.CreatedBy", "System.ChangedDate", "System.ChangedBy", "System.Title", "System.BoardColumn", "System.BoardColumnDone", "System.BoardLane"}, {"System.AreaPath", "System.TeamProject", "System.IterationPath", "System.WorkItemType", "System.State", "System.Reason", "System.CreatedDate", "System.CreatedBy", "System.ChangedDate", "System.ChangedBy", "System.Title", "System.BoardColumn", "System.BoardColumnDone", "System.BoardLane"})
in
    #"Expanded fields"

At this point, you should be able to see the work item field data in the query view, as below. This data set can now be used in the report builder view to create charts and visualisations over the work item data. The code used in the above sample includes all the "System.X" fields from the ADO API, but there will be additional fields available depending on the work item template you use and any customisations you may have made. Now that you have the data in the WorkItems query, formatting the values, renaming the columns, adding calculated fields is all possible as with any Power BI data source.

Next steps

The steps above were deliberately broken down into reusable functions so that this solution can be extended further according to your own requirements. The root of it all - the GetWorkItemsIds function- can be reused to retrieve the list of work item IDs, which could then be passed into any of the other ADO APIs to retrieve different data attributes of the work item either individually or in batches using additional functions.

At a higher level, the same approach - querying for IDs, batching into pages and executing subsequent API calls - can be applied to retrieve other data types via the other entity API endpoints in ADO , or entirely different REST APIs that follow a similar pattern.