“The SharePoint action ‘Get items’ in Power Automate has a field for OData filter query, but I’m not sure what to enter in there.”
The ‘Get items’ (and many other) Power Automate actions can do much more than just return all the items. It can do also some preprocessing to make your life a bit easier later. It can sort the returned items, limit the number of returned items, or filter the results. And this article is dedicated to filtering.
Why should you define the Filter Query
Very often you don’t need all the items. Unless you want to loop through everything and process the items one by one, it’s better to reduce them at the first possibility. And that’s when you’re getting the items. Using a filter directly in the ‘Get items’ action will guarantee that you get only what you need. If you’re building a reminder flow, get only the items behind due date. If you’re looking for tasks for a specific user, filter only his tasks etc.
What to enter in the Filter Query
The Filter Query has a format that’s quite different from everything in Power Automate – you must manually type in the column to search in! And not only that, you must use that column’s internal name!
So on the left side it’s always the column name typed in, followed by an operator (in most cases) and then the value to search for inside single quotes. The value can be then another typed in value:
or a dynamic content:
Note: if you’re looking for a number you can skip the single quotes.
The basic operators are:
| eq | equals | Title eq ‘Tom’ |
| ne | not equals | Title ne ‘Tom’ |
| lt | less than | Number lt 1 |
| le | less or equals | Number le 10 |
| gt | greater than | Date gt ‘2022-10-12’ |
| ge | greater or equals | Date ge ‘2022-10-12’ |
| startsWith | starts with … | startswith(Name, ‘Tom’) |
| substringOf | contains … | substringof(‘Tom’, Name) |
but not all the operators can be used every time. Depending on the column type the filter might have some specifics – unless it’s a simple text column it might be better to find the corresponding filter for that column.
Summary
The Filter Query is one of the most powerful tools in Power Automate. Some solutions are based on using it, e.g. lookups to other lists, but even if it’s not necessary to use it it can always save you some work. If you filter the items right away, you don’t have to filter them later. You get only what you need, and there’s even a cheatsheet to help you with that.
Hi Tom, I’ve referenced a number of your blog posts to grasp concepts, my question is how to filter items returned from a MS forms submission to be able to update a template file and content type columns stored in SharePoint using this approach?
Hello Reg,
I don’t understand what you’re trying to do. Each MS Forms response is it’s own item, there’s nothing to filter, unless you store them somewhere in SharePoint and then you can lookup for the data from a specific response (https://tomriha.com/how-to-lookup-data-in-another-sharepoint-list-in-power-automate/)
thanks a lot for this!