Bug in Excel Online (Business) Connector with Columns Names Having Spaces
Are you struggling with the Excel Online (Business) connector when attempting to Filter, Search, or LookUp records that reference column names with spaces? You are not alone. In this article, we will explore a prevalent issue and its potential workaround. The Excel Online Business connector has some hitches when it comes to handling spaces in column names. Let’s dive deeper into the matter.
Understanding the Bug
Recent forum discussions have indicated an ongoing bug concerning the Excel Online Business connector. The problem arises when we use the Filter, Search, or LookUp functions, specifying criteria referencing column names with spaces. The function then yields an error or returns no data. For instance, if you have a spreadsheet with a column named ‘first name’, any attempts to filter by this column results in an error like:
The requested operation is invalid. Server response: Employee failed: Syntax error at position 10 in ‘First name eq ‘Tim”.
Workaround for the Bug
It’s observed that Power Apps might not correctly escape column names with spaces. However, there’s a way around it. By invoking the RenameColumns function, we can exclude the space from the column name. The formula for the same is:
Filter(RenameColumns(Employee, "First name", "Firstname"), Firstname="Tim")
This method does seem to work on the surface. But, it comes with its set of limitations.
Limitations of the Workaround
On closely monitoring, it’s evident that the request does not incorporate the filter criteria. Such behavior in Monitor indicates Power Apps is managing a non-delegable expression. And what confirms this is when we attempt to filter by the ‘first name’ column for names like “Fred”, which is in row 3, no records are returned. Thus, using RenameColumns results in a non-delegable query.
Conclusion
The Excel Online Business connector seems to falter when it comes to filtering, searching, or looking up records with column names containing spaces. Although there’s a workaround by using the RenameColumns function, it results in non-delegable expressions. Surprisingly, the formula bar doesn’t highlight this as an issue. For those keen on resolving this, you can show your support and vote on the PowerApps Ideas forum.
If you encounter issues or need further clarification on this topic or any technical matters, please don’t hesitate to contact us. We’re here to assist, and should there be any bespoke solutions you need, we can discuss possible charges.
