When Power Automate solutions become slow, the problem is not always the flow logic. In many SharePoint-based automations, the real issue is the amount of data retrieved before that logic begins.
A common design uses Get items to retrieve a large set of SharePoint records and then applies a Condition or Filter array action inside the flow. This may work during development, but it becomes increasingly inefficient as the list grows.
A better approach is to use the Filter Query field in the SharePoint connector. Filter Query uses OData syntax to ask SharePoint to return only the items that meet the required criteria. The filtering happens before the results are sent to Power Automate. The SharePoint connector also supports related optimisation settings such as Order By, Top Count, and Limit Columns by View.
This approach can reduce data transfer, minimise unnecessary loops, simplify flow logic, and improve performance for solutions such as approvals, employee onboarding, document management, service requests, and scheduled reporting.
Why Server-Side Filtering Matters?
Consider a SharePoint list containing 50,000 service requests.
A flow needs only the open, high-priority requests assigned to the Finance team. Without a Filter Query, the flow may retrieve a much larger result set and then evaluate each item individually.
With an OData Filter Query, SharePoint evaluates the criteria first.
For example: Status eq ‘Open’ and Priority eq ‘High’ and Department eq ‘Finance’
Understanding OData Filter Query Syntax
A basic SharePoint Filter Query contains three parts:
| InternalColumnName Operator Value |
Example:
| Status eq ‘Approved’ |
In this expression:
- Status = SharePoint internal column name
- eq = Equal operator
- ‘Approved’ = Value to compare
Depending on the column type, you can use operators such as:
|
Operator |
Meaning |
Example |
| eq | Equals | Status eq ‘Approved’ |
| ne | Does not equal | Status ne ‘Cancelled’ |
| gt | Greater than | Amount gt 1000 |
| ge | Greater than or equal | Score ge 80 |
| lt | Less than | Quantity lt 10 |
| le | Less than or equal | Budget le 5000 |
| startswith() | Text begins with a value | startswith(‘Title’,’INV-‘) |
| substringof() | Text contains a value | substringof(‘Urgent’,Title) |
Important: Always use the internal column name, not the display name. Renaming a SharePoint column changes only its display name, while the internal name remains unchanged.
Using Dynamic Content and Expressions in Filter Query
Filter Query does not need to contain fixed values. Dynamic content can be inserted from:
- A trigger
- Microsoft Forms
- Power Apps
- Variables
- A previous SharePoint action
- Dataverse or SQL
- Calculated Power Automate expressions
Suppose the trigger contains an employee’s email address. The query can be constructed as: Employee/EMail eq ‘@{triggerBody()?[‘EmployeeEmail’]}’
A variable can also be used: Employee/EMail eq ‘@{variables(‘EmployeeEmail’)}’
Filtering Different SharePoint Column Types
The query syntax varies slightly according to the SharePoint column type.
|
SharePoint Column Type |
Example Filter Query |
Important Notes |
| Single Line of Text | Title eq ‘Quarterly Report’ Department ne ‘Finance’ startswith(Title,’INV-‘) substringof(‘Contract’,Title) | Text values must be enclosed in single quotes. If a value contains an apostrophe, escape it by doubling it, for example: ‘CustomerName eq ‘O”Brien Consulting”’ |
| Choice – Single Select | Status eq ‘Completed’ Priority ne ‘Low’ | Use the exact choice value configured in SharePoint. Multi-select Choice columns have limited server-side filtering support. |
| Number | Quantity eq 20 Score ge 80 Age lt 65 | Numeric values must not be enclosed in quotation marks. |
| Currency | Amount gt 1000 Budget le 5000 Rate ne 100 | Currency values are filtered as numbers. Do not use text functions such as startswith() or substringof(). |
| Date and Time | DueDate ge ‘2026-08-01T00:00:00Z’ Created lt ‘2026-08-06T00:00:00Z’ | Use ISO date-time format. For a specific day, use a date range rather than eq, because SharePoint may store a time value. |
| Yes/No | IsActive eq 1 IsArchived eq 0 | 1 represents Yes/True and 0 represents No/False. |
| Lookup – Single Value | DepartmentId eq 12 Department/Title eq ‘Finance’ | Filtering by the lookup ID is usually more reliable because the ID remains unchanged if the displayed value is renamed. |
| Person or Group – Single Value | AssignedTo/EMail eq ‘alex@contoso.com‘ AssignedTo/Title eq ‘Alex Wilber’ | Email is usually more reliable than display name because display names may not be unique. Multi-person fields have filtering limitations. |
Example:
Common Mistakes and Corrections
|
Common Mistake |
Incorrect Example |
Correct Approach |
Why It Matters |
| Using the display name instead of the internal column name | Project Status eq ‘Open’ | Project_x0020_Status eq ‘Open’ | Filter Query requires the SharePoint column’s internal name. |
| Putting numeric values in quotation marks | Amount gt ‘1000’ | Amount gt 1000 | Number and Currency values must not be treated as text. |
| Filtering a Lookup column like a standard text column | Department eq ‘Finance’ | DepartmentId eq 12 or Department/Title eq ‘Finance’ | Lookup fields reference another list. Filtering by ID is usually more reliable. |
| Using exact equality for Date and Time values | Created eq ‘2026-08-05’ | Created ge ‘2026-08-05T00:00:00Z’ and Created lt ‘2026-08-06T00:00:00Z’ | SharePoint may store a hidden time component, so a date range is more dependable. |
| Retrieving every item and filtering later | Get items → Apply to each → Condition | Add the OData condition directly to Get items → Filter Query | Server-side filtering returns fewer records and reduces downstream processing. |
| Using unsupported text functions | endswith(Title,’Report’) or tolower(Title) | Use supported functions such as startswith() or substringof() | SharePoint REST does not support every OData string function. |
| Forgetting quotation marks around text values | Status eq Approved | Status eq ‘Approved’ | Text and Choice values must be enclosed in single quotes. |
| Using the wrong Boolean value | IsActive eq ‘Yes’ | IsActive eq 1 | Yes/No fields use 1 for Yes and 0 for No. |
| Using display names for Person fields | AssignedTo eq ‘Alex Wilber’ | AssignedTo/EMail eq ‘alex@contoso.com’ | Email is usually more reliable and unique than the display name. |
| Not escaping apostrophes in dynamic text | CustomerName eq ‘O’Brien Consulting’ | CustomerName eq ‘O”Brien Consulting’ | Apostrophes must be doubled so the OData query remains valid. |
| Ignoring large-list design | Filtering a 50,000-item list without indexes or pagination | Index frequently filtered columns and enable pagination when needed | Filter Query alone may not be enough for large SharePoint lists. |
Troubleshooting Filter Query Errors
A 400 Bad Request response usually indicates malformed OData syntax.
Check:
- The column’s internal name.
- Whether text values have single quotation marks.
- Whether numbers have been left unquoted.
- Whether the column supports the selected operator.
- Whether a lookup or person property is referenced correctly.
- Whether the date follows an appropriate ISO format.
- Whether a dynamic value contains an apostrophe.
- Whether parentheses are balanced.
Review the Inputs and Outputs of the failed Get items action in the flow run history. The action inputs reveal the exact query sent to SharePoint, which is more useful than reviewing only the expression shown in the designer.
Final Thoughts
SharePoint Filter Query is more than a convenient syntax option. It is an important design technique for building reliable and scalable Power Automate solutions.
By filtering records at the SharePoint source, organisations can reduce unnecessary data processing, simplify workflow logic, and improve flow performance. The value becomes especially clear when SharePoint lists contain thousands of records or when each returned item triggers additional approvals, updates, notifications, or integrations.
The key principles are straightforward:
- Use the correct internal column name.
- Match the syntax to the SharePoint column type.
- Build dynamic values carefully.
- Index frequently filtered columns.
- Use pagination for large lists.
- Combine Filter Query with Top Count and Limit Columns by View.
- Keep unsupported filtering inside the flow to an absolute minimum.
Mastering these patterns helps administrators, consultants, developers, and solution architects build Power Automate flows that remain supportable as data volumes and business requirements grow.
Want to talk?
Drop us a line. We are here to answer your questions 24*7.