OData query filter by results from another query

Hey XELANAMYT ,

You want to retrieve only the necessary data from an Azure DevOps Analytics OData feed by:

1. Querying a list of parent records (like Features).

2. Filtering child records (like User Stories) based on the parent IDs from step 1.

This is to reduce data volume and improve refresh efficiency for daily updates. The Azure DevOps OData API (v3.0) doesn’t support dynamic filters like in (list of values) directly in the query string. That means you can’t fetch child items using something like:

?$filter=ParentId in (123, 456, 789)

So instead, we’ll use Power Query (M) to solve this internally.

Step 1: Get the Parent Items

let
    Source = OData.Feed("https://analytics.dev.azure.com/yourOrg/_odata/v3.0-preview/WorkItems"),
    FilteredParents = Table.SelectRows(Source, each [WorkItemType] = "Feature"),
    SelectedColumns = Table.SelectColumns(FilteredParents, {"WorkItemId", "Title"})
in
    SelectedColumns

This gives you a table of parent items.

Step 2: Extract Their IDs

let
    ParentIds = List.Distinct(FilteredParents[WorkItemId])
in
    ParentIds

This creates a clean list of parent IDs.

Step 3: Get the Child Items

Query the child items (like User Stories or Tasks):

let
    Source = OData.Feed("https://analytics.dev.azure.com/yourOrg/_odata/v3.0-preview/WorkItems"),
    FilteredChildren = Table.SelectRows(Source, each [WorkItemType] = "User Story")
in
    FilteredChildren

Step 4: Filter Children by Parent IDs

Use an inner join to keep only the child items that belong to your selected parents:

let
    JoinedTable = Table.Join(FilteredChildren, "ParentWorkItemId", FilteredParents, "WorkItemId", JoinKind.Inner)
in
    JoinedTable

This creates a final table of child items that are only connected to the previously selected parent items.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam