Power Automate OData Filter Query: Examples, Multiple Conditions and Null Checks

A service desk lead asked me why her reminder flow took forever and still skipped requests. It read the whole Service Requests list into Power Automate, then threw most rows away in a Condition inside an Apply to each loop.

That approach has two problems. The flow moves every item over the wire before it decides anything, and a Get items action without a Top Count only returned 100 items in my test, so requests past the first 100 never reached the Condition at all.

In this tutorial, we’ll write a power automate odata filter query in the SharePoint Get items action so SharePoint does the filtering. We’ll build a test flow, then filter text, Choice, number, date, person and Yes/No columns, combine conditions with and/or, check for null, use dynamic content and fix the errors I hit.

Quick answer: In Get items, open Advanced parameters, add Filter Query and type a condition such as Status eq 'In Progress' and DueDate ge '2026-10-01'. Use the column’s internal name, put text and dates in single quotes, write operators in lowercase (eq, ne, gt, ge, lt, le, and, or), and test blanks with eq null or ne null, without quotes.

What is an OData filter query in Power Automate?

OData (Open Data Protocol) is the URL query language the SharePoint REST API understands. The Filter Query box in Get items is passed to SharePoint as the $filter part of that request, so SharePoint returns only the matching items. You’ll find the other SharePoint actions in the Power Automate tutorials hub.

That’s different from the Filter array action, which runs inside the flow after the data has already arrived. Here’s how the two compare:

	Filter Query (OData) in Get items	Filter array action
Where it runs	On the SharePoint server	Inside the flow, after Get items
Syntax	Status eq 'Closed'	@equals(item()?['Status']?['Value'], 'Closed')
Items pulled into the flow	Only the matches	Everything Get items returned
Functions	eq, ne, gt, ge, lt, le, and, or, startswith, substringof	Any workflow expression (contains, toLower, ticks …)
Best for	Large lists, simple conditions	Logic OData can’t express

I filter with OData first and only fall back to the Filter array action in Power Automate when SharePoint can’t express the condition, such as a case-insensitive “does not contain” check.

Build the test flow step by step

Every example below comes from one flow I built on the SPGuides site. The Service Requests list has 620 items with these columns: Title, Status (Choice: New, In Progress, Closed), Priority (Choice: Low, Normal, High), Requester (text), DueDate (date only) and the built-in Created By person column.

For Yes/No and blank dates, I used a small Task Tracker list with four tasks: one is marked complete and one has no due date. Here’s how to build the same flow:

1. Go to make.powerautomate.com, select Create > Instant cloud flow, name it, pick Manually trigger a flow and select Create.
2. Select the plus sign under the trigger, search for Get items and pick the SharePoint action.
3. Choose your Site Address and List Name from the dropdowns.
4. Open Advanced parameters (or select Show all) and turn on Filter Query and Top Count.
5. Type the condition in Filter Query and set Top Count to the most items you expect, for example 1000.
6. Add a Compose action with length(body('Get_items')?['value']) to see how many items came back, then Save and Test > Manually.

Here’s the Get items action filtering the Choice column. It returned 207 of the 620 items. If you’re new to the action itself, start with how to use Get items in Power Automate.

Two settings matter more than people expect. Top Count is the maximum number of items to return, and in my test a Get items with no Top Count stopped at 100 items. Order By takes an OData sort such as DueDate desc.

OData filter query operators and functions you can use

SharePoint supports a short list of operators. Write them in lowercase, with a space on each side:

Operator / function	Meaning	Tested example	Result (620 items)
eq	Equal	Requester eq 'Megan Bowen'	78
ne	Not equal	Priority ne 'High'	414
gt / ge	Greater than / or equal	ID gt 600 / ID ge 600	20 / 21
lt / le	Less than / or equal	ID lt 11	10
and / or	Combine conditions	Status eq 'New' or Status eq 'In Progress'	414
startswith()	Text starts with	startswith(Requester,'Megan') and Status eq 'Closed'	26
substringof()	Text contains	substringof('printer',Title)	52

There’s no contains(), endswith(), tolower() or not() in a SharePoint filter. I tested contains() and the 400 error it throws in the Get items filter query contains error, along with substringof() and startswith().

Microsoft’s REST documentation also lists date functions like month() and year(). In my Get items test, month(DueDate) eq 10 failed with “The query is not valid.” Use a date range instead, which I cover below.

Power Automate OData filter query examples by column type

The syntax changes a little with the column type. These are the patterns that worked in my flow, with the real counts.

Single line of text

Put the value in single quotes. Title eq 'Printer jam #1003' returned 1 item, and Requester eq 'Megan Bowen' returned 78. In my tests, startswith() and substringof() ignored upper and lower case too.

Choice column

Compare the Choice column directly with its text value: Status eq 'In Progress' returned 207 items. Don’t add /Value the way you would in an expression. Status/Value eq 'In Progress' failed with “The query is not valid.”

Number column

Numbers don’t need quotes. My list has no number column, so I used the built-in ID: ID gt 600 returned 20 items. SharePoint also accepted ID gt '600' (20 again), but leave quotes off so it’s clear you mean a number.

Watch out for numbers stored in a text column. A filter like Age gt '42' on a text column compares text, not numbers, so the results won’t match what you expect. Use a real Number column for ranges.

Date column

Use ISO dates (yyyy-MM-dd) in quotes and a range. Status eq 'In Progress' and DueDate ge '2026-10-01' returned 7 items. A plain eq on a date returned 0 items even when items were due that day. The fixes are in filtering SharePoint items between two dates.

Person column (Created By, Assigned To)

Filter a person column through one of its properties. Author/Title eq 'Bijay Kumar [Microsoft MVP]' and Author/EMail eq '<email>' both returned all 620 items, because I created every item. Author is the internal name of Created By. To test whether a person field has a value, see checking if a person field is empty.

The trap is Author eq 'Bijay Kumar [Microsoft MVP]' without /Title. It ran without an error and returned 0 items. A lookup column works the same way: LookupColumn/Title eq 'value'. Microsoft notes that multi-value person and lookup columns can’t be filtered.

Yes/No column

Use 1 for Yes and 0 for No. On the Task Tracker list, IsCompleted eq 1 returned the one completed task and IsCompleted eq 0 returned the other 3.

This is where flows go wrong quietly. IsCompleted eq true and IsCompleted eq 'Yes' didn’t fail. Both returned 3 items, which are the incomplete tasks, the opposite of what you asked for:

Filter query with multiple conditions: and, or and parentheses

Join conditions with lowercase and or or. Each extra and narrows the result: Status eq 'In Progress' and Requester eq 'Alex Wilber' and DueDate ge '2026-09-01' returned 4 items. The same and/or logic in a Condition action is covered in multiple conditions in Power Automate.

An or across two different columns returns items that match either side. Priority eq 'High' or Requester eq 'Megan Bowen' returned 258 items: the 206 High items plus Megan’s requests that aren’t High.

When you mix and with or, add parentheses. Without them, SharePoint evaluates the and first, and that changed my result:

Filter Query	Items	What SharePoint did
(Requester eq 'Megan Bowen' or Requester eq 'Alex Wilber') and Status eq 'Closed'	52	Closed requests from Megan or Alex
Requester eq 'Megan Bowen' or Requester eq 'Alex Wilber' and Status eq 'Closed'	104	All 78 of Megan’s requests, plus Alex’s 26 closed ones

The first three items from the 52 were SharePoint site request #1008, Password reset #1017 and SharePoint site request #1032, all Closed. I always add parentheses when a filter has both and and or, even when I think the order is right.

Filter query not null and empty checks

To find items where a column is blank, compare it with null, without quotes. On the Task Tracker list, DueDate eq null returned the 1 task with no due date, and DueDate ne null returned the other 3.

You can mix it with other conditions. IsCompleted eq 0 and DueDate ne null returned 2 tasks, Prepare Q4 budget review and Update vendor contract, which are the open tasks that have a due date I can remind someone about.

Don’t put null in quotes. The old pattern DueDate ne 'Null' treats Null as text, and on a date column it failed with this error:

If Get items returns nothing at all, the list itself may be empty. Here’s how to check if a SharePoint list is empty in Power Automate before the rest of the flow runs.

Use dynamic content and expressions in the filter query

The Filter Query box accepts dynamic content and expressions, so the value can come from a trigger, a variable or a Compose action. Type the fixed part, keep the single quotes, and insert the token between them.

In my flow, a Compose action named Requester name holds Alex Wilber. The filter Requester eq '@{outputs('Requester_name')}' and Priority eq 'High' returned 26 items:

DueDate ge '@{formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-dd')}' and DueDate le '@{utcNow()}'

That date version returned 48 items in my earlier date test. Build it in the expression editor, not as typed text. For other date formats, see formatDateTime() in Power Automate.

After Get items, the next step is usually a report or an email. I often send the matching rows to an HTML table in Power Automate and mail it to the team.

Common errors and how to fix them

A failed Get items shows BadRequest on the action, but the run can still say “Your flow ran successfully” when later actions are set to run after a failure. Here’s the full message I captured for an unquoted value:

• The expression “Status eq In Progress” is not valid. Creating query failed. Text values need single quotes: Status eq 'In Progress'.
• The expression “Status eq “New”” is not valid. Double quotes don’t work. Use single quotes.
• The expression “Status EQ ‘New'” is not valid. Operators must be lowercase.
• Column ‘Statuss’ does not exist. It may have been deleted by another user. The name is misspelled or isn’t the internal name. Check it in List settings: select the column and read the end of the URL after Field=.
• The expression “Due Date ge ‘2026-10-01′” is not valid. That’s a display name with a space. Internal names encode spaces, as in Due_x0020_Date, and a renamed column keeps its original internal name.
• The query is not valid. I got this for Status/Value on a Choice column and for month(DueDate).
• No error, wrong result. IsCompleted eq true and a person column without /Title ran fine but returned the wrong items. Check the count before you trust a new filter.

If you want a flow to start only for certain items, filter the trigger instead with Power Automate trigger conditions. The flow then doesn’t run at all for the rest.

If you’re working with bigger lists or more column types next, these guides pick up where this one stops:

• Get All SharePoint List Items using REST API Pagination in Power Automate
• How to Check If Array is Empty in Power Automate [Using 3 Functions]
• How to Get SharePoint Multi-Select Choice Values in Power Automate?
• Retrieve SharePoint List Items by Created Date Range Using REST API in Power Automate

Frequently Asked Questions