Blog

Excel - All you need to know about the 'Excel Online Business' connector

The traditional way of connecting to an Excel data source through the OneDrive connector imposes serious limitations on the number of records we can retrieve. We can overcome several of these with the Excel Online Business connector, but it introduces a different set of problems. This post introduces the 'Excel for Business' connector, and describes what you need to know.

Excel is a simple and popular data source. However, it imposes size limitations when we connect using the OneDrive, Google Drive, or other cloud connectors. This is the connector that Power Apps uses when we create an auto-generated app using the 'start with data' option, and this is the connector that app builders traditionally use. Specifically, this size limitation prevents us from accessing spreadsheets with more than 2000 rows, or spreadsheets that are greater than 2MB. There are more details in my post here:


If we need to work with larger spreadsheets, the answer is to use the 'Excel for Business' connector. This connector supports much larger spreadsheets that are stored in OneDrive for Business, a SharePoint list, or a SharePoint document library.

This post walks through how to use this connector, including the limitations, and the support for delegation.

How to use the Excel for Business connector.

To use this connector, we add a connection using the 'Excel Online (Business)' connector.


The next step prompts us to select an Excel file from OneDrive for Business or from SharePoint. When we select an Excel spreadsheet and choose a table, Power Apps prompts us to choose a primary key field.


If the target spreadsheet doesn't include a unique column, we can choose the option to insert an auto generated id column into the Excel table. This effectively adds a "_PowerApps_ID" column, in the same way that the OneDrive connector works.

Once we add the data source, we can access the spreadsheet data as we would expect, through gallery controls and formulas.

As a tip - notice how the icon colour of the connector is green, rather than the blue colour of the traditional OneDrive connector. This is useful because in cases where we need to help app-builders, we can more easily identify the connector that they're using by asking if they chose the 'green connector', or the 'blue connector'.

What are the limitations of the Excel Online (Business) connector?

At the time of writing (May 2021), the documentation does not appear to be up-to-date. For example, it still states that the connector does not support Power Apps when in fact it does. Also, there have been positive improvements in the way that the connector supports multiple users, which are also not reflected that in the documentation.

For reference, the here's the link to the documentation.

The screenshot from the documentation beneath highlights the "issues and limitations", which is applicable mainly to logic apps. Several of these points are out of date, but the document still serves as a useful reference point, particularly during the testing of our apps.


We'll now examine some of these known limitations in a bit more depth.

What's the spreadsheet size limitation with the Excel for Business connector?

In my test, the maximum acceptable Excel spreadsheet size limit was 50MB. When I attempted to upload a large Excel spreadsheet above 50MB, I received the error beneath. 50 MB is is a big improvement over the 2MB limit that the OneDrive connector imposes.

What are the row size limitations with the Excel for Business connector?

The row size limit is 64,000 records. As before, here's the error message that I received when I attempted to attach a table that exceeds this size. Again, this is a big improvement over the OneDrive connector's 2,000 row limit.


Is it possible to access rows above row 2000?

The most restrictive limitation of the OneDrive connector is that it is not delegable, and not possible to access records above row 2,000.

The great news is that the Excel for business connector does not suffer from this same limitation.

To demonstrate, I added a data table and set the items property to a formula that combines the Filter and Sort functions. There were no delegation warnings, and I was able to retrieve records that were interspersed throughout a 64,000 row spreadsheet (the source spreadsheet is ordered sequentially by the PropertyID column).


What functions and operations are delegable with the 'Excel for Business connector'?

To understand which functions and operators are delegable with the Excel for Business connector, I tested each available function and operator against a text column, and the reults are shown in the matrix beneath.

               
Function/
Operator		 Delegable against
Text columns
FilterYes
SortYes
SortByColumnsYes
LookupYes
=, <>
Yes
StartsWithYes
IsBlankYes
SearchYes

One of the most notable findings is that the search function is delegable. This is important because it enables us to carry out a "contains" type search, and it is a function that is not delegable with SharePoint.

As the screenshot beneath shows, there are no delegation warnings when we call the Search function to find records that contain the word "old" in the address field. As we can see from the PropertyID values, the result correctly displays records throughout the 64,000 table.

The 3 most limiting features of the Excel for Business Connector

So far, the Excel connector appears to perform very well. However, it suffers from at least 3 limitations, which we'll now examine.

Limitation 1 - Number columns are not supported

The first limitation is that the connector does not support numeric columns. Let's take the example of the price column in the spreadsheet beneath, which is correctly formatted as a number column (notice how the numbers in the cell are right aligned, which verifies that Excel correctly recognises the values as numbers).


When I connect to this spreadsheet from Power Apps, it recognises the price column as text rather than a number. In the screenshot of a form beneath, notice how the 'abc' icon appears next to the price field, rather than the '123' icon.


It seems impossible to configure Excel or Power Apps to correctly recognise numeric columns. For practical purposes therefore, the connector does not support numeric columns.

This causes a problem because it prevents us from filtering numeric values using operators such as greater than/less than, and it also prevents us from sorting records in numeric ascending or descending order.

Limitation 2 - It's not possible to filter by date values

A second limitation is that although the Excel connector recognises date columns, it's not possible to filter by date columns. If we attempt to filter by a specific date, we receive the following error:

The requested operation is invalid. Server response. Invalid filter source: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith', or 'endswith' is currently supported.


If we attempt to filter a date column using greater than or less than operators, we receive a delegation error. The result always returns no records, even if the filter operation targets results that fall within the first part of the spreadsheet within the limits of the row count setting. This is obviously incorrect behaviour.


Limitation 3 - With text columns, it isn't possible to filter by more than one column

The third limitation is that we can filter by single column only. To highlight the severity of this issue, here's a quick demonstration. It's possible to filter for all records that match country "grenada". Notice here how the first such result relates to PropertyID 17.


It's also possible to filter by PropertyID 17, as shown beneath.


But if we combine both of these two conditions like so, we receive the delegation warning as shown in the screenshot beneath.
Filter(Property, PropertyID="17" And Country="grenada")


What's worse however, is that the output doesn't include the target record, even although it falls within the row count limit (which is set to default value 500 here).

Workaround - How to search or filter by multiple columns

To address the thrid limitation and to search or to filter by multiple columns, we can work around this issue by applying the "With" technique. Here, we apply a with statement to filter first by the condition that excludes most records. We then filter the output further by the second condition.

As an example, here's the syntax to filter records where the country matches "grenada" and the Address1 field starts with "2".
.
With({grenada:Filter(Property, Country="grenada")},
     Filter(grenada, StartsWith(Address1, "2"))
)



To filter by additional columns, we would nest each additional statment inside a with statement. Here's how to extend the above formula to return records that also match PropertyTypeID 2.


With({grenada:Filter(Property, Country="grenada")},
     With({grenada2:Filter(grenada, StartsWith(Address1, "2"))},
          Filter(grenada2, PropertyTypeID="2")
     )
)


As the screenshot above shows, this has enabled us to filter by 3 columns.

Conclusion

In order to work with larger Excel spreadsheets (up to 50MB and 64,000 rows) we can use the Excel for business connector. This provides better delegable support for filter operations, and a benefit is that it supports the use of the search function to carry out "contains" searches.

However, there are several limitations. It doesn't correctly recognise numeric columns, and it doesn't support the ability to filter by date columns. As we might expect, it also doesn't support the storage of images (unlike the OneDrive/Excel connector).

A severe limitation is that it does not natively support the ability to filter by multiple columns. In order to filter by multiple columns, it's necessary to apply delegation workarounds, such as the pattern that utilises the With function.
Previous
Next
Related posts