Blog

Bug - Excel Online (Business) connector fails to search or to filter against columns with a space in the name

When we attempt to Filter, Search, or to LookUp records based on criteria that references column names with spaces, Power Apps returns an error. This post describes this behaviour in more detail.

Following a recent post in the forums, it appears that there is a bug that affects the Excel Online Business connector. When we call the Filter, Search, or LookUp functions and specify criteria that references column names that include a space, the function returns an error returns no data.

Recreation of bug

To recreate this bug, let's take the example of the spreadsheet that looks like this. Notice how the 'first name' column includes a space.


As we expect, we can filter the Excel table by the surname column (which doesn't include a space in the name).


However when we attempt to filter by the 'first name' column, we receive an error that looks like this:

The requested operation is invalid. Server response: Employee failed: Syntax error at position 10 in 'First name eq 'Tim''.


How to workaround this bug

It appears that Power Apps fails to correctly escape column names that include a space. As a workaround, we can call the RenameColumns function to remove the space from the source column.

For this example, the syntax we would use looks like this:
. 
Filter(RenameColumns(Employee, "First name", "Firstname"),
       Firstname="Tim"
)

As the screenshot beneath highlights, no errors appear in the formula bar, and the formula seems to work as expected.


Are there any limitations with this workaround?

The interesting thing about this workaround is this. If we trace the call in Monitor, we notice that the request does not include the filter criteria.

When we notice this type of behaviour in Monitor, it's a signal that Power Apps is processing a non-delegable expression.



Therefore, does calling the RenameColumns function result in a query that's non-delegable? To verify whether this is the case, I set the 'Data row limit' of my app to 2. I then attempted to filter the 'first name' column by the name "Fred" - which corresponds to row 3 in the source data.

As we can see from the screenshot beneath, thisreturns no records, which proves that calling RenameColumns results in a non-delegable query.


Conclusion

The Excel Online Business connector does not correctly support the ability to Filter, Search, or to LookUp records based on conditions that reference column names with a space. If we attempt to workaround this problem by calling the RenameColumns function, this results in a non-delegable expression. The associated issue with this workaround is that the formula bar fails to underline the formula in blue, to indicate that the expression is non-delegable.

I have posted this behaviour as a bug in the ideas forum, and you can vote for it to if you would like to see this fixed.
Previous
Next
Related posts