Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Get Azure DevOps work item daily historical data using OData query

Hi,

 

I have a report where I need to get the daily historical data of a work item. At the moment, I use Azure DevOps's analytics views to get this data by setting the "granularity" to "daily". For example, 

an_clla_0-1667314900275.png

 

I am trying to move away from using analytics views, and to use Odata query to get the same data. However, I don't know how to control the "granularity" of the historical data. When running the query:

 

https://analytics.dev.azure.com/{organisation}/_odata/v4.0-preview/WorkItemRevisions?$filter=WorkItemId eq {workItemId}

 

the historical data I get is at irregular intervals. I assume that this is because a "revision" is made whenever there is a change to the work item, and this query is simply returning these revisions, instead of returning the state of the work item at a regular interval. (Please correct me if wrong). Is it possible to control the granularity and get the query to return the state of a work item at a regular interval?

 

Thank you!

3 REPLIES 3
ferryv
Resolver II
Resolver II

You might be able to use something similar to this (rolling date is set so it only ever returns 3 months, but can be changed to suit) and expand the column with the fields you want (replace <organisation>, <area path> and <project> to suit):

 

let

rollingDate =Date.ToText(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-3),"yyyy-MM-ddZ"),

Source = Json.Document( Web.Contents ("https://analytics.dev.azure.com/<organisation>/_odata",[
RelativePath = "v3.0-preview/WorkItemSnapshot?",
Query=[
#"$apply"="filter(Area/AreaPath eq '<area path>' "
&"and Project/ProjectName eq '<project>' and DateValue ge "& rollingDate & ")"

]])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

 

Anonymous
Not applicable

Hi @artemus , thank you for the suggestion, I will look into it. Another quick question, in my original post, I mentioned: "I assume that this is because a "revision" is made whenever there is a change to the work item, and this query is simply returning these revisions, instead of returning the state of the work item at a regular interval.". Would you be able to confirm this assumption? Thank you!

artemus
Microsoft Employee
Microsoft Employee

Your best bet is to just use the ADO rest APIs, it has additional operations that odata doesn't.

 

To use the REST api:

  1. Go here to craft the URL: Get started with the REST APIs for Azure DevOps Services and Team Foundation Server - Azure DevOps S...
  2. Use the Power Query formula: VSTS.Contents(url) where url is from step 1 (Web.Contents will not work).

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors