web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id : HbgrcbvXmWvWspJ9t5TWtq
Power Platform Community / Forums / Power Automate - Building Flows / Filter using ODATA que...
Power Automate - Building Flows
Unanswered

Filter using ODATA query : column does NOT contains

Like (0) ShareShare
ReportReport
Posted on 18 Dec 2023 16:44:03 by GF-14081615-0 62

Hello everyone !

I am tugging on my hair so bad rn.
I have turned the Internet upside down and nothing came out of it.

Can we filter a "Get items" action from SharePoint connector to " Column does NOT contain String" ?

 

What I have :

I have a list  called "Choices_Workload" of several choices for answers, which can be changed anytime.

I will always have 1 "generic" answer in this list set to Title = "🚫 - No Answer". This item can change its ID, so it would be preferable to filter by the title and not the ID. It might change (emoji or no emoji etc) but the "No Answer" will remain.


I want to get all the items from this list, Except for the "🚫 - No Answer". 

I have achieved to filter my list with 

substringof('No Answer',Title)

atreyu_0-1702917341000.png

This only gets me the generic Answer which is "No Answer", which is the standard and right behavior, so ok.

 

Now, I want to do just the contrary of this : I want to get every other items EXCEPT for the "No Answer" item.

 

I tried to filter by:

  • not(substringof('No Answer',Title))
  • substringof('No Answer',Title) eq false
  • substringof('No Answer',Title) neq true

But nothing works.


I would love to avoir filtering the items after the "Get items" action, as it is poor optimization.
But if you tell me it is the only solution, then it is what it is.

Thanks a lot for your insight !

I have the same question (0)
  • mvn Profile Picture
    mvn 32 on 22 Feb 2024 at 14:37:51
    Re: Filter using ODATA query : column does NOT contains

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

     

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

     

     

     

  • creativeopinion Profile Picture
    creativeopinion 10,474 Super User 2025 Season 2 on 04 Jan 2024 at 14:55:40
    Re: Filter using ODATA query : column does NOT contains

    @atreyu Unfortunately you cannot use an OData Filter Query to filter items that do not contain a string of text. Which is why you need to use a Filter Array action.  

  • GF-14081615-0 Profile Picture
    GF-14081615-0 62 on 04 Jan 2024 at 10:30:57
    Re: Filter using ODATA query : column does NOT contains

    Hi @creativeopinion !
    Thanks a lot for your time, I already know how to filter the datas using the Filter Array action.

    Here, it is not what I am trying to achieve. I want to already filter my results in the Get List Items action. It would allow me to optimize my flow and my data, and to get rid of all the warnings on the data returned.
    If you have any idea on how to write the ODATA query to get data where the title does not contain "No Answer", that would be a blast !

  • creativeopinion Profile Picture
    creativeopinion 10,474 Super User 2025 Season 2 on 18 Dec 2023 at 17:12:54
    Re: Filter using ODATA query : column does NOT contains

    @atreyu I believe you will have to use a Filter Array action to filter out those items.

    creativeopinion_0-1702919424014.png

    Return Count

    Whenever I use a Filter Array action, I always like to return the count of items returned in a Compose action. This is helpful when building a flow and can also be used to troubleshoot your flow.

    Insert a Compose action. Add an Expression. Use the length() function.

    creativeopinion_3-1702919483725.png

     

    Select the Dynamic content tab and insert the value dynamic content from the Get Items action into the length() function.

    creativeopinion_4-1702919537952.png

    Run a test.

     

    To learn more about how to use the Filter Array action, please refer to this recent YT Tutorial I uploaded.

     

    Hope this helps!

    If I helped you solve your problem—please mark my post as a solution ✅.
    Consider giving me a 👍 if you liked my response!

    👉 Watch my tutorials on YouTube
    👉 Tips and Tricks on TikTok

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Telen Wang – Community Spotlight

We are honored to recognize Telen Wang as our August 2025 Community…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Tomac Profile Picture

Tomac 786 Moderator

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 724 Super User 2025 Season 2

#3
stampcoin Profile Picture

stampcoin 290 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans