Forum Discussion
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...
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"