Power Automate’s own “Get items” action nudges you toward the wrong pattern by default: pull the whole list, then filter it inside the flow. It looks harmless on a small list and hits throttling limits the moment a list has real volume behind it. OData filtering, applying the filter at the data source through the Filter Query field, fixes that at the root instead of working around it downstream.

When I built our organization’s document approval system, retrieving thousands of items from SharePoint frequently caused timeouts and throttling errors. Moving the filter into the OData query instead of pulling everything and filtering downstream eliminated those errors outright, because the flow stopped touching the records it was going to discard anyway. The Open Data Protocol (OData) queries and filters data directly at the source (whether SharePoint lists or Dataverse tables), improving performance and reducing what your flow processes.

Basic Syntax

OData filters use a specific syntax to define query conditions. Here are some basic operators:

  • Equality: eq
  • Inequality: ne
  • Greater than: gt
  • Less than: lt
  • Greater than or equal to: ge
  • Less than or equal to: le
  • And: and
  • Or: or
  • Not: not

Common Use Cases

1. Filtering SharePoint List Items

When using the “Get items” action for a SharePoint list, you can apply an OData filter in the “Filter Query” field.

Example: Get all items where the “Status” column is “Active”

Status eq 'Active'

I remember building a task tracking system with over 5,000 items in the list; a filter this simple was the difference between the flow retrieving the active subset it actually needed and running into throttling on every single execution.

That 5,000 number isn’t incidental. SharePoint’s list view threshold caps most operations, including OData queries, at 5,000 items unless the filter runs against an indexed column. A filter on an unindexed column, against a list past that size, doesn’t just run slowly, it can fail outright with a threshold error, independent of whether the filter syntax is otherwise correct. Indexing the column you filter on (covered under Best Practices below) is what keeps the query under the threshold in the first place, not just a performance nicety.

2. Filtering Dataverse Records

When using the “List rows” action for Dataverse, you can apply an OData filter in the “Filter Rows” field.

Example: Get all contacts with the job title “Manager”

jobtitle eq 'Manager'

Advanced Filtering Techniques

1. Multiple Conditions

You can combine multiple conditions using and and or operators.

Example: Get items that are both “Active” and have a “Priority” of “High”

Status eq 'Active' and Priority eq 'High'

In a project management system I built, I used this exact filter to identify critical tasks that needed immediate attention, which took the monitoring dashboard from a query that felt like it was hanging to one that felt instant.

2. Working with Dates

When filtering dates, use the datetime function.

Example: Get items created after January 1, 2023

Created ge datetime'2023-01-01T00:00:00Z'

For a more dynamic approach, I often use expressions like this to find records from the last 30 days:

Created ge datetime'@{formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-ddTHH:mm:ssZ')}'

This datetime'...' literal syntax is OData v3, which is what SharePoint speaks. Dataverse runs on OData v4 and rejects the datetime prefix outright; a Dataverse “List rows” Filter Rows field wants the ISO 8601 string on its own, no function wrapper:

createdon ge '@{formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-ddTHH:mm:ssZ')}'

The two data sources are not interchangeable here. A filter copied from a SharePoint flow into a Dataverse one over this syntax will fail immediately.

3. String Functions

The functions available depend on the data source. SharePoint’s OData implementation supports startswith and endswith, plus substringof for a “contains” check, an older, backwards syntax: substringof('Project', Title) reads as “does Title contain ‘Project’,” not the other way around. Dataverse, on the newer OData v4, supports contains() directly with the argument order you would expect. Using contains() against a SharePoint list is the single most common cause of “my filter throws an error” reports.

Example: Get items where the title starts with “Project”

startswith(Title, 'Project')

For our document management system, we used this to filter contract documents:

startswith(FileLeafRef, 'Contract-')

4. Numerical Comparisons

You can perform numerical comparisons on appropriate fields.

Example: Get items where the “Quantity” is greater than 100

Quantity gt 100

5. Null Checks

Check for null or not null values using the null keyword.

Example: Get items where the “CompletionDate” is not set

CompletionDate eq null

This is particularly useful for finding incomplete tasks or records that need attention.

Real-World Scenarios

Document Approval System

For a document approval workflow I built, I needed to retrieve only pending approvals assigned to the current user. Here’s the OData filter I implemented:

Status eq 'Pending' and AssignedTo/EMail eq '@{outputs('Get_my_profile_(V2)')?['body/mail']}'

This reduced the dataset from thousands of items to just a handful, eliminating the throttling issues I was facing.

Inventory Management

For an inventory tracking system I built, I needed to identify products below a reorder threshold. The filter has to work around a real limitation: SharePoint’s OData filter can compare a column to a literal value, but not two columns to each other, so StockLevel lt ReorderThreshold is not valid even though it reads naturally. If the threshold is a single fixed number, it works as a literal:

StockLevel lt 50 and Status ne 'Discontinued'

If the threshold genuinely varies per product, the field-to-field comparison has to happen after retrieval, either in a subsequent Filter array action or with the SharePoint delegation techniques covered in the canvas apps guide, which hits the identical constraint from the Power Apps side.

Customer Follow-up Automation

To identify customers needing follow-up, we used date comparisons:

LastContactDate lt datetime'@{formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-ddTHH:mm:ssZ')}' and Status eq 'Active'

This filter finds active customers not contacted in the last 30 days, enabling timely follow-ups.

Troubleshooting Common OData Issues

Over years of implementing OData filters, I’ve encountered several common problems. Here’s how to fix them:

1. Invalid Field References

Problem: Your filter returns an error about an invalid field name.

Solution:

  • Double-check the internal name of your SharePoint column (which might differ from the display name)
  • For columns with spaces, use the correct internal name (e.g., “Due_x0020_Date” instead of “Due Date”)
  • For lookup columns, use the correct syntax: LookupField/Title eq 'Value'

2. Date Formatting Errors

Problem: Date filters don’t work as expected.

Solution:

  • Always use ISO 8601 format (YYYY-MM-DDTHH:MM:SSZ)
  • Use the datetime function correctly: datetime'2023-01-01T00:00:00Z'
  • Be explicit about time zones using convertTimeZone() when needed

3. String Value Formatting

Problem: String comparisons fail.

Solution:

  • Enclose string values in single quotes, not double quotes
  • Field and internal column names are case-sensitive; the string values you compare against generally aren’t, for eq against SharePoint text columns
  • For partial matching against SharePoint, use substringof() (note the reversed argument order) or startswith()/endswith(); against Dataverse, contains() is available directly

Best Practices

  1. Use Dynamic Content: Build dynamic content into your filters to make them more flexible and reusable.

Example:

Status eq '@{variables('statusFilter')}'
  1. Use Internal Names, Not Display Names: A field with a space in its display name doesn’t take that space into the filter as a quoted string. SharePoint generates an internal name for it (spaces become _x0020_), and that internal name is what the filter has to use, per the internal-name guidance above.

Example: a column displayed as “First Name” is filtered as:

First_x0020_Name eq 'John'
  1. Optimize Performance: Use indexed columns in your filters when possible to improve query performance.

In SharePoint, I always make sure to create indexes for columns frequently used in OData filters. On our larger lists, that’s the difference between a filtered query finishing normally and one that trips the list view threshold outright.

  1. Test Your Filters: Always test your filters with various data scenarios to ensure they work as expected.

  2. Handle Errors: Implement error handling in your flow to manage situations where the filter might not return any results.

Common Pitfalls and Solutions

  1. Case Sensitivity: Field and internal column names must match exactly. String value comparisons are generally not case-sensitive against SharePoint text columns.

  2. Date Formatting: datetime'...' literals are OData v3 syntax for SharePoint. Dataverse runs OData v4 and expects a bare ISO 8601 string with no datetime wrapper.

  3. Quotation Marks: Use single quotes for string values, not double quotes. Field names with spaces aren’t quoted; they use SharePoint’s generated internal name (First_x0020_Name, not 'First Name').

  4. Complex Data Types: When filtering on lookup fields or complex data types, you may need to use the expanded property name. Numeric Id fields aren’t quoted:

Customer/Id eq 12345
  1. Filter Complexity: Extremely complex filters can impact performance. If your filter has many conditions, consider breaking it down into multiple sequential operations.

I once had a filter with 12 conditions that would time out consistently. By splitting it into two “Get items” actions with simpler filters, the flow ran reliably.

Key Takeaways

  • OData filtering happens at the data source, not in your flow. That’s the entire performance win.
  • The right filter means your flow never touches the records it was going to discard anyway.
  • Index the columns you filter on. On larger lists, that’s what keeps a filtered query under the list view threshold instead of failing outright.
  • Test filters against real data volumes, not a sample list of ten items, before deployment.

Go find the flow that’s still pulling full lists and filtering after the fact. That’s the same fix that took our document approval system from thousands of items and constant throttling down to a handful.