Get Azure DevOps work item daily historical data using OData query

[Anonymous]

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, 

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!

[ferryv]

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]

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]

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).