cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ash_R
Frequent Visitor

Error when applying two filters on Excel using ODATA query. Any workaround?

My flow is triggered based on a form being submitted.

The aim of the flow is to match the ReqID and email against an existing Excel sheet and return the unique row which is then added to another excel. 

 

I'm using the following ODATA query to filter:

 

Email eq 'dynamicemailaddressfromform' and ReqID eq 'dynmaicReqIDfromform'

 

Screenshot attached:

 

Odata query1.png

 

 

The error I get is as follows:

'Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported'

 

Does this mean that even though 'and' is supported by ODATA, I can only use a single 'eq' for the excel connector?

 

If so, is there a workaround for this i.e. to identify the unique row using a combination of ReqID and Email?

Thanks in advance!

 

 

 

 

 

 

4 REPLIES 4
DeepakS
Super User
Super User

Hi @Ash_R  ,

Yes you can use only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' .

This is how i would achieve:

use only one Odata eq expression in your "List Row Present in Table"

than i will use "Filter Array" action on the  "List Row Present in Table" results  to filter with second condition.

Excel.PNG

 

 

——————————————————
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

v-bacao-msft
Community Support
Community Support

 

Hi @Ash_R ,

 

When configuring Filter Query in List rows present in a table action, we can only configure a single filter, and temporarily do not support using “and” to configure multiple filters.

Please check this idea and vote for it:

https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Have-Consistency-in-on-Filter-Query-and-Ord... 

Also as @DeepakS described, Filter array is a good choice, please take a try and feel free to let us know if you have any other questions.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ash_R
Frequent Visitor

Thanks @DeepakS 

 

With Filter Array, I couldn't figure out a way to get the info into an excel file for further analysis.
I was able to create an HTML table but struggled to get this back into Excel.

 

So here's what I did instead:
In my source data (excel), I created a new blank table. This had the same column names and formatting as the original table. 

In the flow

 

  • List rows present in the original table and used ODATA query to filter based on first parameter
  • Apply to each, Add a Row into the new table
  • Get a Row from the new table and used ODATA query to filter based on second parameter

 

Will post a screenshot as well!

 

Hi @Ash_R ,

 

You can use the Parse JSON action to parse the body of the Filter array, and then insert the dynamic content of the Parse JSON output into a new Excel table.

http://johnliu.net/blog/2018/6/a-thesis-on-the-parse-json-action-in-microsoft-flow

 

Best Regards,

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

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (65,837)