OData filter query builder
Get items and List rows actions are much faster when the filtering happens at the source instead of in a loop afterwards, but OData syntax is easy to get wrong: eq instead of =, quotes around text but not numbers. Add your conditions and copy the query.
OData Query
Add conditions, combine them with AND or OR, then add $select, $orderby and $top.
This builds a single AND/OR group, not nested groups within groups. For deeper nesting, combine two of these queries manually.
How to use it
- Add your conditions and choose whether they combine with AND or OR.
- Optionally set the columns to return, the sort order and a row limit.
- In an action with separate fields, paste each part without its prefix: the text after
$filter=goes in Filter Query, and so on. For a direct API call, use the whole query string.
Nothing you type here is sent anywhere. The tool runs entirely in your browser.
OData filter syntax, with examples
Each condition is a column name, an operator and a value: Status eq 'Approved'. Use the column’s internal name, not the name you see in the list. In SharePoint a column shown as “Due Date” may be DueDate or Due_x0020_Date: open the column’s settings and read the name after Field= at the end of the address. In Dataverse it’s the logical name, such as emailaddress1.
Comparison operators
| Filter | Returns rows where |
|---|---|
Status eq 'Approved' | Status equals Approved |
Status ne 'Closed' | Status is anything except Closed |
Amount gt 500 | Amount is greater than 500 |
Amount ge 500 | Amount is 500 or more |
Amount lt 500 | Amount is less than 500 |
Amount le 500 | Amount is 500 or less |
Text goes in single quotes and numbers don’t. If the text itself has an apostrophe, write it twice: Surname eq 'O''Brien'.
Combining conditions with and, or and brackets
| Filter | Returns rows where |
|---|---|
Status eq 'Open' and Priority eq 'High' | both conditions are true |
Status eq 'Open' or Status eq 'On hold' | either condition is true |
(Status eq 'Open' or Status eq 'On hold') and Priority eq 'High' | Status is Open or On hold, and Priority is High |
Write and and or in lower case, as shown. Without the brackets in the last example, the and is applied first and you get every Open row plus only the high-priority On hold rows.
Text: startswith and contains
SharePoint and Dataverse use different functions here, so a text filter copied from one won’t work in the other.
| Filter | Returns rows where |
|---|---|
startswith(Title, 'INV-') | Title begins with INV- (SharePoint and Dataverse) |
substringof('Smith', Title) | Title contains Smith (SharePoint Get items) |
contains(fullname, 'Smith') | fullname contains Smith (Dataverse List rows) |
endswith(emailaddress1, '@contoso.com') | the email address ends with @contoso.com (Dataverse only) |
The builder’s “contains” option writes the SharePoint form, substringof, with the value first. For Dataverse, change it to contains with the column first.
Empty values: null
| Filter | Returns rows where |
|---|---|
DueDate eq null | DueDate is empty |
DueDate ne null | DueDate has a value |
Write null without quotes. 'null' in quotes looks for the word “null”.
Yes/No, choice and lookup columns
| Filter | Returns rows where |
|---|---|
Approved eq 1 | a SharePoint Yes/No column is Yes (0 for No) |
donotemail eq true | a Dataverse Yes/No column is Yes |
Department eq 'Finance' | a SharePoint choice column is Finance (filter on the choice text) |
statecode eq 0 | a Dataverse choice column has the value 0 (filter on the number, not the label) |
_parentcustomerid_value eq @{triggerOutputs()?['body/accountid']} | a Dataverse lookup points to that record (the lookup name wrapped in _ and _value, and the ID without quotes) |
Dates
| Filter | Returns rows where |
|---|---|
Modified ge '@{addDays(utcNow(), -7)}' | a SharePoint item changed in the last 7 days (date in quotes) |
modifiedon ge @{addDays(utcNow(), -7)} | a Dataverse row changed in the last 7 days (date without quotes) |
The @{...} parts are expressions: type them in the Filter Query box exactly as shown, or add them from the Expression tab. To show a date in a readable format afterwards, use the formatDateTime generator.
More free tools
Power Automate expression builder
Pick a function, fill in the values, copy a working expression.
Trigger condition and filter builder
Conditions in, a trigger condition or Power Fx Filter formula out.
formatDateTime generator
Pick a date source and a format, get the formatDateTime expression.
SharePoint column formatting generator
Conditional background colour for a list column, as ready-to-paste JSON.
SVG to Power Apps image converter
Paste SVG markup, get a data URI for the Image property.
Parse JSON schema generator
Paste a sample payload, get the schema for a Parse JSON action.
Parse JSON schema loosener
Fix Parse JSON failing on null values or missing fields.
Adaptive Card builder
Title, body and Approve / Reject buttons, as card JSON for Teams.
Flow error handler (Try / Catch)
Run-after settings, error expression and alert body for a Try / Catch scope.
HTML email table styler
Column headers and a colour, as an inline-styled email table.
FetchXML builder
Dataverse’s own query language, for List rows or Advanced Find.
Microsoft Forms pre-filled link builder
Let an AI fill in a whole form at once, then check it and submit it yourself.
Browse every tool
All the free tools on one page.