Using OData Lambda Expressions to Filter on Nested Collections and Related Tables in Power Automate

I’m ashamed to admit this, but I just recently learned this really cool feature.

You can create “Lambda expressions” in OData filters to filter records by items that appear in a nested collection (array) or in a related table.

Here are some pages with official documentation on the subject:

Filter rows using OData (Microsoft Learn)

Basic Tutorial (OData.org)

How it Works

To use the Lambda expression in an OData filter, you specify the nested table column name (or relationship name for a related table) with a forward slash.

Then you use either any() or all() depending on whether you want any match to suffice or if you need the expression to result in true for every item in the collection.

Next you supply a name for each item in the collection (like an inline variable name). Usually it’s a single letter, but you could use longer strings. Put a colon after the name.

Finally you write the expression to be evaluated for each item in the collection.

<related_collection_name>/any(x:<expression to evaluate on all items>)

You can reference columns/properties in the collection with forward slashes.

E.g.

<related_collection_name>/any(x:x/<property_name_in_related_collection> eq '<some_string>')


Get Outlook Contacts Example

Here I’m using the “Get contacts (V2)” action to list contacts. I want to filter it for the contact with a specific email address.

Unfortunately, normal OData expressions won’t help because the email address for each contact is stored in a nested table called “emailAddresses” in a property called “address”.

Here Lambda comes to the rescue.

emailAddresses/any(e:e/address eq '[e-mail]')


Dataverse Examples

Lambda expressions are very useful when filtering Dataverse tables for records that have certain child records. It saves you from having to create a longer FetchXML filter to accomplish the same thing.

Filter Accounts to Which Certain Contacts are Linked (N:1)

Here I want to list Accounts that have certain child Contacts. I’ll look for any Account with a Contact that has “Harkonnen” in the “fullname” field.

First I need to get the name of the relationship between Accounts and Contacts.

This can be found in make.powerapps.com by selecting the table and navigating to the “Relationships” list.

For the OData Lambda filter, I just need to type in the relationship name followed by a “/” then any(c: contains(c/fullname,'Harkonnen'))

contact_customer_accounts/any(c:contains(c/fullname,'Harkonnen'))

I could also use another field, such as last name.

contact_customer_accounts/any(c:contains(c/lastname,'Harkonnen'))

And I could also use a different expression than contains, for example a straight equals expression.

contact_customer_accounts/any(c:c/lastname eq 'Harkonnen')

Here I find one account, “House Harkonnen” that has child contacts with the lastname of “Harkonnen”.

Filter Records by Numeric Column on Related Table (N:1)

You can also use numeric comparison operators like “Less than” or “Greater than or Equal to” on columns on related tables with the Lambda ODatA expression.

I have a “States” table with USA States, and a “Cities” table that is linked to the “States” table, with population data for each city.

So if I wanted to list all states with a city that has a population of a million or more, I can do it easily with Lambda.

mln_City_mln_State/any(c:c/mln_population ge 1000000)

When it runs, I get these states:

Filter Users by Security Role (N:N)

You can also easily filter a table by records with an N:N relationship, which can be very helpful in some situations.

In this example, I’ll list all Users with a “System Administrator” Security Role.

The “Security Role” table has an N:N relationship with the “User” table. It’s called “systemuserroles_association”.

The lambda expression looks like this:

systemuserroles_association/any(r:r/name eq 'System Administrator')

I also add in a filter to only return Users with a “Main Phone” number, so that I can filter out non-human users.

systemuserroles_association/any(r:r/name eq 'System Administrator') and address1_telephone1 ne null

It works!

If you found this post helpful please give it a like.
Power Platform Developer | Wordpress | LinkedIn: Mark Nanneman | YouTube: Mark’s Power Stuff | Buy me a coffee | Power Platform Community