Excel - Vote to add numeric column support to the 'Excel Online Business Connector' connector!
The Excel Online Business connector does not correctly support number / numeric columns. This post describes this behaviour in more detail, and provides a link where you can vote to fix this limitation.
This limitation causes a number of problems. The first is that it isn't possible to sort the numeric column in the correct numeric sequence. The second is that it won't be possible to filter values by comparison operators, such as the greater than and less than operators.
Recreation of problemTo highlight this problem, I created an Excel spreadsheet with a 'price' column that is formatted as a number. As the screenshot beneath shows, Excel correctly recognises this as a numeric column because it right aligns the cell values.
Next, I converted these values to a table and upload the spreadsheet to OneDrive.
Next, I created a connection to this data source from Power Apps using a Excel Online Business connector.
recognises the price column as text rather than a number,
despite the column being defined as a number in Excel.
In the screenshot
beneath that shows a form that'ss connected to the Excel table, notice how the 'abc' icon appears next to the price field,
rather than the '123' icon.
Note - this limitation does not affect OneDrive / Excel connector.
To clarify this behaviour further, this same behaviour exists with the 'OneDrive for Business' connector.
However, this problem does not exist with the standard OneDrive connector. If I save the exact same spreadsheet to a personal OneDrive account and connect using the OneDrive connector, Power Apps correctly recognises the price column as a numeric column.
- How to use formulas to perform calculations in Excel and to display the results in Power Apps
- Tools - Convert Excel to Collections Online
- Excel - What to be aware of when building apps with Excel - Service Rate Limit
- Dataverse - Excel Import - How to fix Microsoft ACE OLEDB 12.0 Error
- Bug - Excel Online (Business) connector fails to search or to filter against columns with a space in the name
- Excel - All you need to know about the 'Excel Online Business' connector
- Data - Excel vs Google Sheets