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?