OData query filter by results from another query

[XELANAMYT]

Hi,

I'm using OData feed connections to get data from Azure DevOps (analytics.dev.azure.com) for my org. 

I can get all the data that I want without issue, however I get far too much of it, much of which I'd discard in subsequent filtering.

Is there a way that you can utilise the results of one query to filter a second query?

A simplified example would be:

1. First query odata feed to get the Parent entity data
2. Extract just the Ids of the list of Parent entities
3. Second query to get all of the Child entities where the ParentId is in that list

I've read several articles which frequently suggest parameters (which I've never used before), however they seem to be static values.

The main reasons I want to do this is to reduce the amount of unneeded data (this report will need to be refreshed at least daily), and to use multiple queries to populate different tables in my model. I can use a single query to get all the data in one go, but then I'd just end up with one table and if I used subsequent queries to split into more tables referencing the first then the single query would be ran each time, that in turn would make refreshing even more resource/time consuming.

Any suggestions would be welcomed.

Thanks

[v-karpurapud]

Hi @XELANAMYT 

Welcome to the Microsoft Fabric Community Forum.
 
Reducing the volume of data retrieved from the Azure DevOps Analytics OData feed by filtering child records  based on a dynamic list of parent record IDs . While this would typically be achieved using an in clause in many query languages, the Azure DevOps OData API does not support dynamic filtering using in expressions directly in the query string, as mentioned by @Nasif_Azam 

For reference, review the official Microsoft documentation which outlines the supported OData operations in Azure DevOps Analytics:
Construct OData queries for Analytics - Azure DevOps | Microsoft Learn
Analytics query tools - Azure DevOps | Microsoft Learn


If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[Nasif_Azam]

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