Accessing Description in AzureDevops from PowerBI via oData API?

[Anonymous]

I am currently using Odata feed in PowerBI power query to access User Stories and their fields from Azure Devops in order to build reporting. I am unable to access the 'description' field using the Odata feed or the analytics views, does anyone know a way that I can pull this?

[ferryv]

Not sure if anyone is still looking for this, but you can get this as follows:

let
GetDocument = Json.Document(VSTS.Contents("https://dev.azure.com/{your Azure DevOps org}/{your project}/_apis/wit/workitems/{id}")),
ConvertToTable = Record.ToTable(GetDocument[fields])
in 
ConvertToTable

Replace {your Azure DevOps org}, {your project} and {id} as applicable.

You end up with something like this:

Transposing the table, promoting the first row as headers, and additional transformations will give you the info you need.

[noneother]

@ferryv wondering how to get a table of all the latest workitem data rather than having to specify a single workitem ID?

[ferryv]

The standard odata feed provides all info in a project, so depening on what you require, that might be sufficent. See also: https://learn.microsoft.com/en-us/azure/devops/report/powerbi/odataquery-connect?view=azure-devops. 

The standard feed does not retrieve longtext fields (Description, Acceptance Criteria, etc). The article referenced in one of the provious comments provide and end-to-end solution via a loop. 

[caseylide]

Thanks for your reply! I am indeed still looking for a solution to this thread. I will have a look at your idea, try it out, and let you know how it goes. 

[ferryv]

No problem. As this seems to come up a lot, I decided to write an article on LinkedIn about retrieving the Description and Acceptance Criteria with an end-to-end process for a fictional Azure DevOps organisation and project that might help you on your way. The process would be the same for other work item types also (e.g. Risks, Bugs, etc), but the fields might be slightly different.

[Anonymous]

Thank you for Sharing this Article, it's really helpful.. am able to retrive the Acceptance Criteria with out any HTML code.. but if acceptance criteria is empty for any of the row.. am getting an error. how to solve this error.

[ferryv]

Change the last step in the process from 

#"Remove HTML from Acceptance Criteria" = Table.AddColumn( #"Add lf for li (AC)","Acceptance Criteria", each Text.Combine(Html.Table([VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria],{{"AC",":root"}})[AC],"#(lf)"))

to 

#"Remove HTML from Acceptance Criteria" = Table.AddColumn( #"Add lf for li (AC)","Acceptance Criteria", each if [VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria] = null then null else Text.Combine(Html.Table([VSTS.fields.Microsoft.VSTS.Common.AcceptanceCriteria],{{"AC",":root"}})[AC],"#(lf)"))

Essentially, the change checks if the original value is null and if it is, it keeps it as null (or you can change it to any other value, like "No acceptance criteria provided", otherwise it strips the html out.

That should resolve the issue you're having. 

Thanks for pointing that out. I have updated the referenced article above also with the fix. 

[Anonymous]

Thank you, it is working as expected… my Final Ask.... is it possible to pull the extract data for more than one projects with in Same Organization

[ferryv]

I don't see any reason why you shouldn't be able to, although I have not worked through that specific scenario. 

I wrote an article on Linkedin a while back on how to retrieve data from multiple Azure DevOps organisations - Link to the article. This also works across different projects and area paths within a single Azure DevOps organisation.

If you read through this and modify the example in that article with the code to retrieve longtext fields, you should be good to go. 

[Anonymous]

Thank you for your response. I haven't seen this article earlier.... the previous article helped me a lot.. ..i will go through this latest one...

[Subasri]

Even i am looking out for options to fetch multiline fields like Acceptance criteria , Description... etc.. from Azure Devops to power bi .. Can someone please help me with steps to get the fields using API i am clueless and any small help too will be helpful....

[JRS_LoriC]

I am also wanting to build a report in Power BI which bring in all Change Request work items with their descriptions. I can build a report in the AzDO Analytics views but I can't pull across the data? Please advise.

[v-jingzhang]

Hi @Anonymous 

If the default Analytics views and Odata feed cannot meet your need, you can try create a custom Analytics view and connect to it using Azure DevOps connector. Or use OData queries to connect and query the fields you need.

Reference:

Use OData queries to generate Power BI reports - Azure DevOps | Microsoft Docs

Tasks & concepts using Data & Analytics - Azure DevOps | Microsoft Docs

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[caseylide]

The response doesn't seem to answer the question or address the issue. Here I will try to address the same problem using my own scenario...

I am already using odata from Power BI to query work items in Azure Devops. The problem is that the query fails whenever I try to retrieve the "Description" field on work items. I can get other fiels such as Title and WorkItemID to return successfully in the odata query, but not the Description field. Why not? How can this be resolved?