Free · runs entirely in your browser

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

  1. Add your conditions and choose whether they combine with AND or OR.
  2. Optionally set the columns to return, the sort order and a row limit.
  3. 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.
A Dataverse List rows action in Power Automate with the Add new parameters list open, showing Select columns, Filter rows, Sort By, Expand Query, Fetch Xml Query, Row count, Skip token and Partition ID.
In a Dataverse List rows action, open Add new parameters and choose Filter rows, Select columns and Sort By for the tool’s $filter, $select and $orderby. SharePoint’s Get items has the same idea, under Filter Query and Order By. Screenshot: Microsoft Learn, CC BY 4.0.

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

FilterReturns rows where
Status eq 'Approved'Status equals Approved
Status ne 'Closed'Status is anything except Closed
Amount gt 500Amount is greater than 500
Amount ge 500Amount is 500 or more
Amount lt 500Amount is less than 500
Amount le 500Amount 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

FilterReturns 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.

FilterReturns 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

FilterReturns rows where
DueDate eq nullDueDate is empty
DueDate ne nullDueDate has a value

Write null without quotes. 'null' in quotes looks for the word “null”.

Yes/No, choice and lookup columns

FilterReturns rows where
Approved eq 1a SharePoint Yes/No column is Yes (0 for No)
donotemail eq truea Dataverse Yes/No column is Yes
Department eq 'Finance'a SharePoint choice column is Finance (filter on the choice text)
statecode eq 0a 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

FilterReturns 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