Power Automate filter on SharePoint Lookup column

“I need to get all SharePoint items with the same value in a lookup column, but the OData filter in Power Automate doesn’t return any items. What should the filter look like to work properly?”


To filter items in Power Automate it’s important to understand how the SharePoint lookup column works. Lookup column creates a connection between an item in one list and another item in a second, lookup list. A dropdown field with data from a selected column from the lookup list will be added to the form.

The one thing to notice here is that you can change the column from the lookup list anytime. If you change it, SharePoint will update the lookup data automatically. That means, there must be a connection that’s not related to the data itself, but by item ID instead. The lookup column in the main list contains an ID of the item in the lookup list. And that’s the data you must use in the filter itself, the lookup item ID.

SharePoint change lookup column

Use ‘Lookup id’ in the Filter Query

As already explained, Filter Query needs ID of the item from the lookup list. It must be a number, so in case you’re using lookup column dynamic content you should use the lookup ‘Id’.

<LookupColumnMainList> eq '<LookupItemIDLookupList>'

e.g. where 'Lookup' is the name of the Lookup column
Lookup eq '61'
Lookup eq '@{items('Apply_to_each_5')?['Lookup/Id']}'
Power Automate filter query on lookup column

The Filter Query above will return only items with the same lookup value. Just make sure you use the internal name of the lookup column.

Summary

To get more from the OData Filter Query you should understand what you’re filtering. Not only the data and its type, but also how is the column implemented in SharePoint. Once you understand that the lookup data is stored as an item ID to the lookup list, building the filter itself is a simple task.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to a special content like a SharePoint Filter Query cheat sheet.

Zero spam, unsubscribe anytime.

Add a Comment

Your email address will not be published. Required fields are marked *