Forum Discussion

feric80's avatar
feric80
Copper Contributor
Oct 21, 2023

Azure Devops Analytics and filters on Iteration/EndDate on a snapshot

Hello,

 

I have the following query: 

 

let
    Source = OData.Feed("https://analytics.dev.azure.com/isaac-rd/ISAAC%20Platform/_odata/v4.0-preview/WorkItemSnapshot?" 
    & "$apply=filter(" 
        & "(WorkItemType eq 'Bug') " 
        & "and DateValue ge 2023-09-01Z " 
        & "and DateValue eq Iteration/StartDate " 
    & ")" 
    & "/groupby(" 
        & "(DateValue), " 
        & "aggregate($count as Count) " 
    & ")", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4])
in
    Source

 

This returns the expected data. No problem.

 

Now if I simply change this previous query to match the date value with the EndDate of the iteration, I get ZERO results: 

 

let
    Source = OData.Feed("https://analytics.dev.azure.com/isaac-rd/ISAAC%20Platform/_odata/v4.0-preview/WorkItemSnapshot?" 
    & "$apply=filter(" 
        & "(WorkItemType eq 'Bug') " 
        & "and DateValue ge 2023-09-01Z " 
        & "and DateValue eq Iteration/EndDate " 
    & ")" 
    & "/groupby(" 
        & "(DateValue), " 
        & "aggregate($count as Count) " 
    & ")", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4])
in
    Source

 

 

Now at first I thought it might be that for some reason the EndDate was empty in Devops but when I remove that portion of the query (and add the iteration start and end to the resuls)  like so: 

let
    Source = OData.Feed("https://analytics.dev.azure.com/isaac-rd/ISAAC%20Platform/_odata/v4.0-preview/WorkItemSnapshot?" 
    & "$apply=filter(" 
        & "(WorkItemType eq 'Bug') " 
        & "and DateValue ge 2023-09-01Z " 
    & ")" 
    & "/groupby(" 
        & "(DateValue, Iteration/StartDate, Iteration/EndDate), " 
        & "aggregate($count as Count) " 
    & ")", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4]),
    #"Expanded Iteration" = Table.ExpandRecordColumn(Source, "Iteration", {"StartDate", "EndDate"}, {"Iteration.StartDate", "Iteration.EndDate"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Iteration",{{"Iteration.EndDate", Order.Ascending}, {"DateValue", Order.Ascending}})
in
    #"Sorted Rows"

 

I can clearly see in the printed results that I have matches between DateValue and Iteration/End but still I always get Empty results for Iteration/EndDate but it works perfectly with Iteration/StartDate.

 

Here are the results highlighted: 

 

Anybody have a clue what's going on?

 

 

 

Resources