CODEWITHSUNDEEP
azure-devopspowerbiodata
user6767022
user6767022

Reputation: 105

Return Only Most Recent Record From Related Entity in OData Query

I am trying to create an OData Query to return Bugs from Azure DevOps for a PowerBI report, but I am not getting the results I am looking for, as one of the Related Entities that I am trying to expand returns multiple results.

My base Query looks like this (simplified & removing custom fields)

https://analytics.dev.azure.com/[organization]/[project]/_odata/v3.0-preview/WorkItems?$select=WorkItemId,WorkItemType,Title,State,LeadTimeDays&$filter=WorkItemType eq 'bug'&$expand=Teams($select=TeamName,AnalyticsUpdatedDate)

Some records return multiple Team Names in the JSON Response

"value": [
{
  "WorkItemId": 16547,
  "LeadTimeDays": 173.0639004,
  "Title": "test",
  "WorkItemType": "Bug",
  "State": "Closed",
  "Severity": "3 - Medium",
  "Teams": [
    {
      "TeamName": "Team1",
      "AnalyticsUpdatedDate": "2019-09-17T01:48:46.5433333Z"
    },
    {
      "TeamName": "Team2",
      "AnalyticsUpdatedDate": "2019-12-03T16:52:39.9466667Z"
    }
  ]
}

]

I can't tell why these records have multiple values for this Entity, but I only need the most recent (Team 2 in the example above). Is it possible to return only the most recent record for the Related Teams Entity? I've tried using orderby and top on the expand clause and other places in the query to no effect. If I can't do it in the OData query, then I can accomplish it in Power BI after expanding the Table.

Upvotes: 1

Views: 1291

Answers (1)

user6767022
user6767022

Reputation: 105

I found how to solve this. I needed semicolons between the clauses within the Expand clause.

https://analytics.dev.azure.com/[organization]/[projet]_odata/v3.0-preview/WorkItems?$select=WorkItemId,WorkItemType,Title,State,LeadTimeDays&$filter=WorkItemType eq 'bug'&$expand=Teams($select=TeamName,AnalyticsUpdatedDate;$orderby=AnalyticsUpdatedDate desc;$top=1)

Upvotes: 1

Related Questions