Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
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"
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!
Your best bet is to just use the ADO rest APIs, it has additional operations that odata doesn't.
To use the REST api:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!