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
Super User
Super User

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

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.

Highlighted
Community Support
Community Support

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

 

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

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

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!

Community Support
Community Support

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

 

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
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (6,747)