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

'IN' operator equivalent in OData PowerAutomate

Hello my dudes/dudettes,

 

I am trying to filter an Excel file for relevant data and bring it into PowerAutomate with OData.

I am using the "List rows present in table" to access an Excel file and using the OData filter query to execute a query equivalent to an "IN" statement in SQL. So for example, say I have a table called equipment with columns like equipment_name and description, I have a list of equipment names and I want to retrieve the information for all equipment in that list- the SQL of equivalent of this would be something like SELECT * FROM equipment WHERE VALUE IN (the list of equipment).

My following questions are as follows:

  1. Is the "IN" operator supported in PowerAutomate?

  2. I have tried something but I am getting an error. Now, I do not know if it's because "IN" is unsupported in PowerAutomate or it's something else. See picture below

    1. I use the "Filter Query" field to enter my "IN" statement where leftmost "Outputs" is name of the column I want to fetch from and rightmost "Outputs" is the list of equipment names.

    2. n6r70h0tekg51.png

       

       

2. When I run the flow, I get a Bad Request error which means there is a syntax error.

 

3. Below is the query that ran:

 

Complete_Asset_Description in ["CE1307||Mini Grinder||110v - Grinder - GA4530R -16amp||675351/R","CE1313||Sump Pump||RSD400 - 110v Sump pump||N/A"]

4. The error message returned and says there is a syntax error at position 29.

{
"status": 400,
"message": "Syntax error at position 29 in 'Complete_Asset_Description in [\"CE1307||Mini Grinder||110v - Grinder - GA4530R - 16amp||675351/R\",\"CE1313||Sump Pump||RSD400 - 110v Sump pump||N/A\"]'.\r\n inner exception: Syntax error at position 29 in 'Complete_Asset_Description in [\"CE1307||Mini Grinder||110v - Grinder - GA4530R - 16amp||675351/R\",\"CE1313||Sump Pump||RSD400 - 110v Sump pump||N/A\"]'.\r\nclientRequestId: d392aa5a-0d5a-4011-9df4-b6d8147140c4",
"error": {
"message": "Syntax error at position 29 in 'Complete_Asset_Description in [\"CE1307||Mini Grinder||110v - Grinder - GA4530R - 16amp||675351/R\",\"CE1313||Sump Pump||RSD400 - 110v Sump pump||N/A\"]'.\r\n inner exception: Syntax error at position 29 in 'Complete_Asset_Description in [\"CE1307||Mini Grinder||110v - Grinder - GA4530R - 16amp||675351/R\",\"CE1313||Sump Pump||RSD400 - 110v Sump pump||N/A\"]'."
},
"source": "
excelonline-uks.logic-ase-uksouth.p.azurewebsites.net"
}

 

 

Please advise on this.

Thanks,

A random dude.

3 REPLIES 3
rohentz
Community Support
Community Support

Hi @Hm20 how are you?

 

@Hm20 regarding your question, as documented at https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#known-issues-and-limitations the "List rows present in a table" actions has the following limitations:

 

Supports basic filtering and sorting:
- Supports the following filter functions: eq, ne, contains, startswith, endswith.
- Only 1 filter function can be applied on a column.
- Only 1 column can be used for sorting

 

Unfortunately, the in operator is not yet supported by this action.

 

Please let me know if you have any question or concerns about the solution provided.

 

Regards.

 

Rodrigo Hentz

Microsoft Support Engineer

 

Hm20
Frequent Visitor

Cool thanks for the reply. Bit of a shame in my opinion😶

 

Stay safe.

rohentz
Community Support
Community Support

Thanks @Hm20 for the reply

 

Regards.

 

Rodrigo Hentz

Microsoft Support Engineer 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (3,176)