“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.
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’.
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.
not contains doesn’t work with odata filter query but can use ‘not’ like this:
$filter=not(startswith(fieldName,’text to search’))