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

Use a ‘not contains’ operator in Power Automate Filter Query

Posted on November 2, 2022December 18, 2022 by Tom

“I know there’s an operator to return all SP items that contain a string, but what about not contains, how to do such filter in Power Automate?”


The Filter Query field is one of the most powerful fields when working with Power Automate. Whenever you get the option to filter, to reduce the number of items you’ll process, you should do it. But its functionality is quite limited when working with text. The only operators you can use are equals, not equals, startswith() and substringof() (=contains), and that might be not enough. What about the reverse, for example ‘not contains’? Filter all items that don’t contain a specific string, e.g. email addresses from outside of your organisation?

There’s no such Filter Query

Unfortunately there’s no such option in the OData Filter Query. While there’re some sources recommending to use the listdata.svc service, e.g.

/_vti_bin/listdata.svc/PlaygroundList?$filter=substringof('title',Title) eq true

…it doesn’t work. All it returns is a generic error message.

Power Automate Filter not contains

Since such request won’t do the trick, you’ll have to use the no-code approach.

Use Filter array action instead

As with the Multiple lines of text column, where the problem is an unsupported column, you’ll have to get all the items first.

Get all the items with the ‘Get items’ action, without any filter. Make sure that you increase the item limit to include all the items.

Once you have the items, you can start the filtering. The ‘Filter array’ action will allow you to implement more filters than the Filter Query, one of them being ‘does not contain’.

Power Automate Filter not contains

Configure the filter and you’ll get only the items that don’t contain the specific string. Just make sure that you use the output of the ‘Filter array’ and not the ‘Get items’ later in the flow, and you access the right values!

Summary

If you need a filter that’s not supported by the OData Filter Query, you still have an option to use the ‘Filter array’ action instead. It’s not the best approach as you have to get all the items first and it’s more complicated to access the values later, but it’ll do the filtering – the most important part.


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.

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