Answered

List rows present in a table - Filter Query not working

(0) Share

Report

Posted on by Ganeshkumar_R

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

Categories:

Using connectors

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

v-litu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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'])

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.

Was this reply helpful? Yes No
Ganeshkumar_R 20 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-litu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No