How to Filter SharePoint Items by a Lookup Value in Power Automate
Question details
The user needs to filter SharePoint list items based on a lookup column value (0 or 1) using Power Automate, but the lookup value does not appear in the dynamic content menu.
- Product
- SharePoint and Power Automate
- Device & OS
- not provided
- Scenario
- Automating an email workflow in Power Automate that isolates and emails records based on unclaimed Case IDs from a related SharePoint list.
- Observed behavior
- The lookup column value is unavailable as dynamic content in Power Automate, preventing straightforward conditional filtering.
Ensure you have edit permissions for the Power Automate flow and sufficient access rights to both the primary and related SharePoint lists.
Use an OData Filter Query or Additional 'Get Items' Action
Bypass the dynamic content limitation by writing a specific OData expression or by nesting an additional 'Get item' action to evaluate the lookup value.
Lookup columns in SharePoint are complex object types. Because they reference entire items in another list, Power Automate often does not expose their sub-properties directly in the dynamic content pane. To resolve this, you must query the underlying ID or use explicit expressions.
In your Power Automate flow, insert a 'Get items' action and connect it to your primary SharePoint list.
Expand the advanced options in the 'Get items' action. In the 'Filter Query' field, type the internal name of your lookup column followed by the referenced field, for example: LookupColumnName/Id eq 0.
If the direct OData query fails, add an 'Apply to each' loop iterating through the primary list. Inside the loop, add a 'Get item' action targeting the referenced list using the lookup ID, then apply a 'Condition' to check if the returned value is 0.
Looking for a Lightweight Document Management Solution?
While Power Automate handles complex list automations, WPS Office provides a streamlined, highly compatible, and free alternative for editing, managing, and collaborating on your daily office documents without the enterprise complexity.
- 1. Download and Install: Get the free WPS Office installer from the official website and complete the quick installation process.
- 2. Open Your Office Files: Launch WPS Office and directly open any existing .docx, .xlsx, or .pptx files seamlessly.
- 3. Save and Collaborate: Edit your documents and save them locally or sync them via WPS Cloud for easy collaboration.
Frequently Asked Questions
Why is my SharePoint lookup column missing from dynamic content?
Lookup columns return complex objects rather than simple text or numbers. Power Automate restricts displaying complex objects in dynamic content menus. You must extract the value using specific OData filter queries or expressions.
Can I filter a SharePoint list by a lookup column's text value?
Yes, you can filter by a lookup column's text value by using an OData filter query like LookupColumnName/Title eq 'YourValue' inside the 'Get items' action settings.
Does WPS Office integrate with SharePoint document libraries?
WPS Office supports standard WebDAV integrations and can open files from mapped network drives, allowing you to edit and save files directly to local or network-synced SharePoint document libraries.
