Skip to main content

Notifications

Community site session details
Power Automate - General Discussion
Answered

ODATA Filter Query with contains operator

Like (1) ShareShare
ReportReport
Posted on 30 Jun 2022 01:21:34 by 4

Having some issues with the ODATA query when using "List Rows Present in the Table" to build the flow.

 

I have an excel file that contains a few columns and one of the columns is "Username".

I am using the "List Rows Present in the Table" to lookup this row from the excel file based on an input that comes via another source.

I need to check this using the "contains" operator but not able to figure out the correct syntax to apply in the Filter Query field.

Tried a number of different ways but they all end up in errors.

How can I get this resolved?

 

The error I get when running the flow is the following:
Syntax error at position 17 in 'Username contains juarestrepo'.
inner exception: Syntax error at position 17 in 'Username contains juarestrepo'.
clientRequestId: 4de78408-ae00-49ed-81aa-d70bc5e3ab38

 

Attached is also the screenshot of the "Get List Rows Present" Screen Shot 2022-06-30 at 11.14.30 am.pngScreen Shot 2022-06-30 at 11.16.50 am.png

 

 

body":{"status":400,"message":"Syntax error at position 17 in 'Username contains juarestrepo'.\r\n inner exception: Syntax error at position 17 in 'Username contains juarestrepo'.\r\nclientRequestId: 4de78408-ae00-49ed-81aa-d70bc5e3ab38","error":{"message":"Syntax error at position 17 in 'Username contains juarestrepo'.\r\n inner exception: Syntax error at position 17 in 'Username contains juarestrepo'."},"source":"excelonline-wus.azconn-wus.p.azurewebsites.net"}

 

 

  • lars-moe Profile Picture
    2 on 11 Apr 2024 at 06:54:56
    Re: ODATA Filter Query with contains operator

    I don't understand this.

     

    With this code:

     

    GroupCollectionResponse groupCollectionResponse = await GraphClient.Groups.GetAsync((requestConfiguration) =>
    {
    requestConfiguration.QueryParameters.Select = new string[] { "id", "displayName" };
    requestConfiguration.QueryParameters.Filter = "contains(displayName, 'SLS')";
    });

     

    I get this exception:

      Name Value Type

    â—˘InnerException{"Filter operator 'Contains' is not supported."}System.Exception {Microsoft.Graph.Models.ODataErrors.ODataError}

     

  • KFlanagan Profile Picture
    14 on 28 Jun 2023 at 14:52:15
    Re: ODATA Filter Query with contains operator

    Hi @kgreen8790  thank you for that information. Is there an article or MS page that helps breakdowns all the options to filtering OData? 
    I ask that since I would like to learn more about the overall knowledge. My current question is can we filter by "Contains Data(Column_A)"

  • carlilelance Profile Picture
    351 on 01 Dec 2022 at 22:07:59
    Re: ODATA Filter Query with contains operator

    This should be the accepted solution.

  • kgreen8790 Profile Picture
    189 on 26 Sep 2022 at 13:47:23
    Re: ODATA Filter Query with contains operator

    Glad I could help

  • Verified answer
    JAWL Profile Picture
    391 on 26 Sep 2022 at 10:22:30
    Re: ODATA Filter Query with contains operator

    @kgreen8790 , it's okie, I just found the solution by trial and error. We can use

    not contains(Description,'User_Input') 

  • JAWL Profile Picture
    391 on 26 Sep 2022 at 10:17:21
    Re: ODATA Filter Query with contains operator

    @kgreen8790 , appreciate your helpful answer. What if I want "not contains" operator?

     

    not contains(Description,'User_Input') ?

     

     

  • kgreen8790 Profile Picture
    189 on 06 Sep 2022 at 18:33:00
    Re: ODATA Filter Query with contains operator

    Here's the proper format for contains in Odata filter query: contains(Column_A, 'text to filter')

    See screenshot for additional info.

     

    Be sure to mark my response as "solution" if this has solved your issue.debug1.PNG

  • Sachin_M Profile Picture
    4 on 30 Jun 2022 at 08:55:51
    Re: ODATA Filter Query with contains operator

    @Shuvam-rpa I will try your suggestion and that might work, will post the result here.

    I am concerned about the size of data loaded as overtime this file will grow significantly in size so loading all rows and then filtering will this lead to any issue due to number of records?

  • Shuvam-rpa Profile Picture
    889 Super User 2024 Season 1 on 30 Jun 2022 at 04:38:44
    Re: ODATA Filter Query with contains operator

    @sachinmishra If the above reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

  • Shuvam-rpa Profile Picture
    889 Super User 2024 Season 1 on 30 Jun 2022 at 04:19:49
    Re: ODATA Filter Query with contains operator

    @sachinmishra I would recommend you to leave odata filter blank in list rows present in a table action and then after it create filter array action where you can filter the record using contains . please check below screenshot as reference

     

    Shuvamrpa_0-1656563784285.png

    Shuvamrpa_1-1656563838917.png

    If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

     

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

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - General Discussion

#1
David_MA Profile Picture

David_MA 8 Super User 2025 Season 1

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 8 Super User 2025 Season 1

#3
ankit_singhal Profile Picture

ankit_singhal 7 Super User 2025 Season 1

Overall leaderboard