How to Fix Power Automate OData Filter Exclude Multiple SharePoint Values Error
Question details
The user is experiencing an invalid-query error in Power Automate when trying to exclude multiple SharePoint choice-column values using the substringof function and eq false.
- Product
- Power Automate
- Device & OS
- not provided
- Scenario
- Filtering items in a Power Automate Get Items action using OData queries to exclude multiple SharePoint choice values.
- Observed behavior
- The Get Items action returns an invalid-query error when using substringof with comparisons like eq false to exclude several values, though changing OR logic to AND logic helps.
Ensure you have the necessary edit permissions for the Power Automate flow and the target SharePoint list before modifying the Get Items OData filter queries.
Use Explicit Not-Equal Conditions with AND Logic
Replace complex substringof functions with explicit ne (not equal) conditions combined with 'and' logic to properly exclude multiple values without triggering an invalid query error.
The substringof function combined with eq false can cause invalid-query errors in Power Automate, especially when dealing with multiple exclusion criteria in SharePoint choice columns. Using explicit ne (not equal) conditions is a more reliable way to format OData queries for SharePoint.
Navigate to your Power Automate flow in edit mode and click to expand the specific 'Get Items' action that is causing the invalid-query error.
Locate the 'Filter Query' field under the advanced options and delete the existing query that relies on substringof and eq false.
Construct a new filter query using the 'ne' operator. For example, to exclude 'ValueA' and 'ValueB' from a column named 'Status', enter: Status ne 'ValueA' and Status ne 'ValueB'.
Click the Save button in the upper right corner of Power Automate, then run a manual test to verify the flow successfully executes and retrieves the correctly filtered SharePoint items.
Looking for a Lightweight Office Suite to Process Exported SharePoint Data?
While Power Automate manages your complex workflows and data transfers, you often need a reliable office suite to analyze and format the exported SharePoint list data. WPS Office is a free, lightweight, and highly compatible alternative to Microsoft Office, making it the perfect tool for handling exported Excel reports and documents.
- 1. Export SharePoint List Data: Export your filtered SharePoint list data or Power Automate outputs to an Excel (.xlsx) file.
- 2. Open in WPS Spreadsheet: Launch WPS Office and open your exported data to quickly view and verify your Power Automate results.
- 3. Analyze and Report: Utilize built-in formulas, pivot tables, and professional formatting tools to manage your records effortlessly.
Frequently Asked Questions
Why does the substringof function return an invalid query error in Power Automate?
The substringof function can be unreliable with SharePoint choice columns when passed through Power Automate's Get Items action. When combined with logical negations like 'eq false' and multiple OR conditions, the underlying SharePoint REST API often rejects it. Using explicit equality or inequality checks (eq or ne) is strictly recommended.
Can I use the 'or' operator when excluding multiple items in an OData filter?
No. If you want to exclude multiple specific values simultaneously, you must use the 'and' operator (e.g., Status ne 'Closed' and Status ne 'Cancelled'). If you use 'or', the item will be included if it simply doesn't match one of the conditions, which undermines the exclusion logic.
How do I correctly filter a SharePoint choice column in Power Automate?
To filter a Choice column, you generally need to append '/Value' to the column's internal name in your OData query. For example, if your column is named 'Category', your explicit filter query should be formatted as: Category/Value eq 'SpecificChoice'.
