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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

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

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.

Users online (3,274)