Complete Guide to OData Operators in Power Automate: Filtering SharePoint List Items
When setting up filter queries in Power Automate’s “Get Items” action, you need to specify them using OData filter query syntax.
I’ve noticed there isn’t much comprehensive information available about OData queries, so I decided to compile this practical guide. Update 2/14/2024: Recently, I’ve been relying more on GPT to generate query expressions rather than writing them manually. I’ve written another article about this approach.
OData (Open Data Protocol) is a protocol used for HTTP-based data exchange and manipulation. If you search for “What is OData,” you’ll find many excellent explanations. I encourage those interested to explore further.
For the most detailed information about OData filter specifications and syntax, the official Microsoft documentation provides the most comprehensive resource:
In this article, we’ll create a SharePoint Online list and a Power Automate flow to demonstrate how to apply filters using the “Get Items” action in real-world scenarios.
Here’s what our sample list looks like:
And here’s the Power Automate flow we’ve prepared to test various filters:
Note: You can ignore the final Apply to each action as it’s only there to verify that the filters are working correctly.
Comparison Operators
Let’s start with comparison operators:
eq : equals
ne : not equals
ge : greater than or equal to
gt : greater than
le : less than or equal to
lt : less than
Here are practical examples:
Equals: eq
To retrieve items where the Number column equals 3, use this syntax:
The result shows only items where the Number column equals 3:
Not Equals: ne
To retrieve items where the Title column is not ‘ccc’, use this syntax: ※Note: Single quotes (”) are optional for number comparisons but mandatory for string and date comparisons
The result shows all items except the one with title ‘ccc’, retrieving four items in total:
Greater Than: gt
To retrieve items where the Number column is greater than 3, use this expression:
The result shows only two items meeting this criteria:
Less Than: lt
To retrieve items where the Number column is less than 3, use this expression:
The result returns only items with Number values 1 and 2:
Greater Than or Equal To: ge
To retrieve items where the Number column is greater than or equal to 3:
Here are the successful results:
Less Than or Equal To: le
Finally, to retrieve items where the Number column is less than or equal to 3:
Here are the successful results:
Logical Operators
When combining multiple conditions, use these logical operators:
and : AND operator
or : OR operator
() : Parentheses (for precedence)
Here are practical examples:
AND Operator: and
To retrieve items where the Number column is greater than or equal to 3 AND the Title is not ‘ddd’, use this expression:
The query successfully returns items with titles ‘ccc’ and ‘eee’:
OR Operator: or
To retrieve items where the Number column is greater than 4 OR the Title is ‘aaa’, use this expression:
The results show items with Number = 5 and Title = ‘aaa’, confirming the query worked correctly:
Parentheses ()
You can use parentheses in filter queries to control the order of operations.
For example, to find items where (Number is 1 or 4) AND (Title is ‘aaa’ or ‘ccc’), use this expression:
The query returns one item that matches these combined conditions, demonstrating successful filtering:
Note: When SharePoint List Columns are Created in Japanese
If you’ve created SharePoint columns using Japanese names, you’ll need to take a few extra steps to use them in OData queries.
First, access your SharePoint list settings:
Click on the column that was created with a Japanese name:
You’ll be taken to the column’s settings page. In the address bar, locate and copy the FieldID value. To use this column in queries, add “OData_” as a prefix to this FieldID – this becomes your column name for filtering.
For example, to filter by the “数字” (Number) column in our sample list, use this expression:
The query successfully returns only items where the “数字” column equals 8:
コメント