CODEWITHSUNDEEP
azure-devopspowerbiodata
Geezer
Geezer

Reputation: 73

Accessing Description and Acceptance criteria in AzureDevops User Stories from PowerBI via oData API

I am trying to retrieve the Description and Acceptance Criteria fields from the user story work item type from Azure DevOps from PowerBI.

I have tried the oData feed, and also configuring an analytics view, and scoured the online documentation all to no avail.

Has anyone managed to ever do this successfully? (What I actually want to do is establish the existence and/or the length of both fields).

Upvotes: 7

Views: 8881

Answers (1)

Eric Smith
Eric Smith

Reputation: 2560

Description and Acceptance Criteria fields are not available via the oData feed.

If you want to see what is available check out the entity model at this url.

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/v3.0-preview/$metadata

What you can do is supplement the OData feed in PowerBI with calls to the Azure DevOps Rest API, to get the Description and Acceptance Criteria fields. You can do that via PowerBI functions.

Have a look at these two articles by Colin Dembovsky and Wouter de Kort to get an idea of what's possible. They include some good examples of supplementing the feed data with Rest calls.

Edited with an example to get you started:

Your custom function can look like this

let
    Source = (project as text, workItemId as text) =>
let
    Source = Json.Document(Web.Contents("https://dev.azure.com/{ORG-NAME}/" & project & "/_apis/wit/workitems/" & workItemId & "?api-version=5.1"))
in
    Source

Then your Query using the OData feed and calling the function

let
    Source = OData.Feed("https://analytics.dev.azure.com/{ORG-NAME}/{PROJECTNAME}/_odata/v3.0-preview", null, [Implementation="2.0"]),
    WorkItems_table = Source{[Name="WorkItems",Signature="table"]}[Data],
    #"Changed Type1" = Table.TransformColumnTypes(WorkItems_table,{{"WorkItemId", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type1", "Description", each GetWorkItemAdditionalData([ProjectName], [WorkItemId])),
    #"Expanded Description" = Table.ExpandRecordColumn(#"Invoked Custom Function", "Description", {"fields"}, {"Description.fields"}),
    #"Expanded Description.fields" = Table.ExpandRecordColumn(#"Expanded Description", "Description.fields", {"System.Description", "Microsoft.VSTS.Common.AcceptanceCriteria"}, {"Description.fields.System.Description", "Description.fields.Microsoft.VSTS.Common.AcceptanceCriteria"})
in
    #"Expanded Description.fields"

Upvotes: 7

Related Questions