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 |
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.
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
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.