Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

What to enter as Filter Query in the Power Automate SP actions

Posted on October 12, 2022October 16, 2022 by Tom

“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:

eqequalsTitle eq ‘Tom’
nenot equalsTitle ne ‘Tom’
ltless thanNumber lt 1
leless or equalsNumber le 10
gtgreater thanDate gt ‘2022-10-12’
gegreater or equalsDate ge ‘2022-10-12’
startsWithstarts with …startswith(Name, ‘Tom’)
substringOfcontains …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.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

2 thoughts on “What to enter as Filter Query in the Power Automate SP actions”

  1. Reg Ferreira says:
    November 4, 2022 at 12:15 pm

    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?

    Reply
    1. Tom says:
      November 13, 2022 at 6:32 pm

      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/)

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes