Skip to main content

Notifications

Power Automate - Using Connectors
Answered

List rows present in a table - Filter Query not working

(0) ShareShare
ReportReport
Posted on by 20

Hi All, My excel sheet has 5000 rows, I need to filter the row for specific values in particular column using filter query.

 

For example: the below mentioned is the sample excel data, "Column" is the column name it has not space in between. Need to filter the row which has Nam1. I passed Column eq 'Nam1' in OData filter query is not working. In tried in two account, worked in trial account but not in actual account.

So planned to use the Filter Array but it has case sensitive, i need to get all row having Nam1, not to use contains

  

Input:                                  

Column
Nam1
Nam2
nam1
NAM1
Nam1(1) 

 

Expected Output:

 

Column
Nam1
nam1
NAM1
  • v-litu-msft Profile Picture
    v-litu-msft on at
    Re: List rows present in a table - Filter Query not working

    Hi @Ganeshkumar_R,

     

    Filter query is case sensitive, the filter query in Excel connector only support inserted one.

    If you want to insert multiple conditions like, Column eq 'Nam1' or Column eq 'nam1', it is impossible.

    If the Excel table has rows over 5000, these lines are placed in an array every 5000 lines in batches, and then filter array is performed.

     

    Best Regards,
    Community Support Team _ Lin Tu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ganeshkumar_R Profile Picture
    Ganeshkumar_R 20 on at
    Re: List rows present in a table - Filter Query not working

    Thanks for reply.

    Yes, we can use to lower function, but looping all 5000 rows is not efficient right.  So only I'm trying to fix the filter option in List rows present in a table. I also changed the threshold value to 5000 in setting. Can you tell why the filter is not working?

    Thanks

  • Verified answer
    v-litu-msft Profile Picture
    v-litu-msft on at
    Re: List rows present in a table - Filter Query not working

    Hi @Ganeshkumar_R,

     

    You could use toLower() function to convert the column value to string in lowercase format, then check if they equals to "nam1":

    toLower(item()?['Column'])

    Screenshot 2021-01-26 104626.jpg

     

    Screenshot 2021-01-26 104823.jpg

     

    Best Regards,
    Community Support Team _ Lin Tu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard

Featured topics