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:
Is the "IN" operator supported in PowerAutomate?
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
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. 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.
Cool thanks for the reply. Bit of a shame in my opinion😶
Stay safe.
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