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 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @XELANAMYT 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[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 · Accepted solution]

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

[XELANAMYT]

Thanks @Nasif_Azam for your response. I was concerned that this would be the only option and I'd have to get both large sets of data and filter internally in PowerBI. I just never like the idea of getting masses of data of which I'll throw away 90% of it within the first few steps!