Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Outlook
    • Planner
    • Power Apps
    • SharePoint
    • Teams
  • Triggers
  • Ready to use solutions
    • Approval Process Solution
    • Task Delegation App
    • The Ultimate Power Automate expressions cheat sheet
    • Power Automate HTTP requests to SharePoint cheat sheet
    • Power Automate HTTP requests to Graph API cheat sheet
  • ABOUT ME
  • Get help with your flow
  • POWER PLATFORM SERVICES
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.


πŸš€ Master Power Automate

Join 2,100+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

1 thought on “Use a ‘not contains’ operator in Power Automate Filter Query”

  1. manoj nair says:
    February 22, 2024 at 3:37 pm

    not contains doesn’t work with odata filter query but can use ‘not’ like this:

    $filter=not(startswith(fieldName,’text to search’))

    Reply

Leave a Reply Cancel reply

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

πŸš€ Master Power Automate

Join 2,100+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

Still exchanging emails to get things approved?

Get the Approval Process solution and the Task Delegation App to skip the hard part and deploy an automated, fully functional approval solution on a SharePoint list in minutes! And then the next one, and the next one...

Approval Template Preview ✨ Learn more ✨

Turn ideas into flows with ease!

Grab the complete Power Automate Cheat Sheet Bundleβ€”everything you need to master expressions, SharePoint HTTP calls, and Graph API in Power Automate.

Cheat Sheet Bundle Preview ✨ Get the Cheat Sheets

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with thousands of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2025 Let's POWER Automate | Theme by SuperbThemes