cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonybakker
Regular Visitor

Excel List Row present in Table - Filter Query - "lt" does not work yet "eq" does

I have a excel work book with about 200 records. The first columns has a unique ID numbers from 4621 to 4688.  When I enter a filter "UpdateID eq 4632" (without the double quotes) it will return one row.  If i change the eq to lt (less than) it will error out.

 

"status": 400,
"message": "Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.
 
According to other posts and Odata documentation it should work and not give this message.  Does "Excel List Row present in table" accept LT, GT, LE and GE checks?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Jcook
Super User III
Super User III

Hello @tonybakker 

Please take a look at the solution in this post.

https://powerusers.microsoft.com/t5/Building-Flows/Filter-Query-ne-null-doesnt-work/td-p/253467

It uses a Filter array

 

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

Thanks, I read that post you gave as link earlier today. I just did not slow down to read it thoroughly. 🙂

 

I removed the filter from the Excel List and added the Filter array step as shown. 

 

I can see the "Filter Array" is a easier and more flexible way to filter.  Still puzzled though, that the oData filter could not do a simple filter using LT less than operator.

 

Thanks again!

View solution in original post

4 REPLIES 4
Jcook
Super User III
Super User III

Hello @tonybakker 

Please take a look at the solution in this post.

https://powerusers.microsoft.com/t5/Building-Flows/Filter-Query-ne-null-doesnt-work/td-p/253467

It uses a Filter array

 

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





View solution in original post

Thanks, I read that post you gave as link earlier today. I just did not slow down to read it thoroughly. 🙂

 

I removed the filter from the Excel List and added the Filter array step as shown. 

 

I can see the "Filter Array" is a easier and more flexible way to filter.  Still puzzled though, that the oData filter could not do a simple filter using LT less than operator.

 

Thanks again!

View solution in original post

@tonybakker 

I was reading an article on the Odata filter for excel connector. I was trying to find before posting here but I cannot find anymore.

 

I cant remember if the filters were going to be updated eventually or not. But yes filter array seems to do things way easier.

 

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others


Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete





DudelerDad
Frequent Visitor

I was able to use contains in the Filter field right in the 'List rows present in a table' to filter out blank rows. I am using an string field that contains email addresses field so 'contains(email,'@domain.edu') and In my case the @domain.edu would all be the same.

Didn't try just contains(email,'@') but it should probably work.

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (3,061)