Answered

Differences in Excel Connector Behaviour - 'One Drive for Business' vs 'Excel Online (Business)'

(1) Share

Report

Posted on by suelaw1954

I'm building a Business Analytics System which is going to pull data from various sources, including spreadsheets on a sharepoint site and I'm having problems with the new 'Excel Online (Business)' connector.

Normally I would build one of the automatic apps for comparison, but the 'Excel Online (Business)' connector is not available via that path yet, so I copied the spreadsheet to my OneDrive and built one of the mobile apps for that. I've run into several problems as the connectors seem to have different behaviour patterns.

1. (fixed) The 'One Drive for Business' connector is quite happy if you have hyphens in your column headers, the 'Excel Online (Business)' connector sees the hyphen as a programming element and spits the dummy. I fixed this by changing all the hyphens in my column headers to underlines, but it is annoying. I include this as information for other people if they are having this problem.

2. Still having headaches with the 'Excel Online (Business)' connector not recognising some functions.

The 'One Drive for Business' connector automatic app uses the following to control the Browse screen via the search text box:

SortByColumns(Search([{at sign}TableName], TextSearchBox1.Text, "ColumnHeader1","ColumnHeader2"), "ColumnHeader1", If(SortDescending1, Descending, Ascending)) (names have been changed for privacy)

When I put the equivalent term in a canvas app with an 'Excel Online (Business)' connector the AppChecker is quite happy until I try to run it when I get:

"The requested operation is invalid. Server Response: AnnualLabAllocations failed: Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported. clientRequestId: 408da0cb-1989-4e04-a10e-fb37d420c9a1"

(This also temporarily appears in the AppChecker, but rapidly disappears when I try to get the details).

Questions:

Can I confirm that it is the "Search" function causing the problems?

What is the best replacement function?

Can I still use two target arguments for the text search (i.e. search two columns)?

Thanks in advance

Sue Law

Categories:

Building Power Apps

All responses (8)

Answers (1)

Sort by

poweractivate 11,078 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

@suelaw1954 As a possible workaround in case maybe you can export Excel table to SharePoint like this: https://support.microsoft.com/en-us/office/export-an-excel-table-to-sharepoint-974544f9-94bc-4aa8-9159-97282d256dab then use the SharePoint as the data source - see if you still have the same problems. If you do, then maybe there is really a formatting issue with the document. Also, compared with Excel, it may be possible to have more functionality with less delegation issues when using SharePoint compared with Excel as well.

In case you must use Excel for sure as a data source, you can try and see if the conversion process above helps.
If it does not, you can use Filter, StartsWith / EndsWith instead of Search if you are looking for an alternate function to Search to try with.

Check if this can help as a starting point.
suelaw1954 28 on at

Like
a
(1)

Report
Copy link

Link copied!

I'll try creating the list and see if that sorts things, but I need to acquire data from a wide range of people, some of whom are a bit luddite when it comes to sharepoint, so I'd like to have multiple options for data entry (including directly into an excel table plus a PowerApp for those who hate messing with excel).
Sue
suelaw1954 28 on at

Like
a
(1)

Report
Copy link

Link copied!

I've exported to a List, saved the App under a different name and changed the data source to the List.
The following works (and is the auto-generated form, "Title" is the actual column name specified and is equivalent to "ColumnName1"):
SortByColumns(Filter([@ListName], StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))
Doesn't work if I replace Title with ColumnName1.
This doesn't work:
SortByColumns(Search([@ListName], TextSearchBox1.Text, "Title", "Year_Updated"), "Title", If(SortDescending1, Descending, Ascending)).
So it looks as if the Search function doesn't work with Sharepoint.
Sue
poweractivate 11,078 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@suelaw1954

If Search is not working, check for the following:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup#syntax

Search(Table*, SearchString, Column1 [, Column2, ... ] )
Table - Required. Table to search.
SearchString - Required. The string to search for. If blank or an empty string, all records are returned.
Column(s) - Required. The names of columns within Table to search. Columns to search must contain text. Column names must be strings and enclosed in double quotes. However, the column names must be static and cannot be calculated with a formula. If SearchString is found within the data of any of these columns as a partial match, the full record will be returned.

Check if the column "Year_Updated" contains text. Maybe if it is not a text column, it might not work correctly with Search.
Also try and see if Search works on just the "Title" column for troubleshooting as well.

If you were able to get it to work, now just try it on Excel data source directly and see if it might just work there too after that, and if it does, maybe this could solve your problem.
suelaw1954 28 on at

Like
a
(1)

Report
Copy link

Link copied!

@poweractivate

That's crazy, the autogenerated app (via OneDrive connector) quite happily 'search'es numeric columns.
But it might be touchy about it with the Excel Online connector.
Sue
suelaw1954 28 on at

Like
a
(1)

Report
Copy link

Link copied!

@poweractivate
It IS crazy. I've just removed the second (numeric) column in the search function and all is sweetness and light.
It's the lack of consistency within PowerApps which is driving ME crazy.
Sue
suelaw1954 28 on at

Like
a
(1)

Report
Copy link

Link copied!

@poweractivate
Just removed the second column from my original App and things are working.
Unfortunately I don't have any other text columns in this particular spreadsheet so I can't tell whether it's because the column is numeric or because the second column is there.
Still, stick to one search column in Apps using the Excel Online connector.
Thanks for all your help,
Sue
Verified answer

timl 37,287 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @suelaw1954

I'm not sure if this helps, but your findings here are all correct.

The Excel Online connector does not work against numeric or date columns. I have a post in the ideas forum that you can vote for if you would like this feature added to Power Apps.

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Excel-Online-Business-Connector-Add-support-for-number-numeric/idi-p/936194#M33739

Note that it also doesn't support columns with spaces, and I have another post in the ideas forum here.
https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Bug-Excel-online-business-connector-fails-to-search-filter/idi-p/956656#M33866

The Excel Online connector can search against a single column only. I have a post here that describes this issue.

http://powerappsguide.com/blog/post/how-to-use-excel-online-business-connector

With SharePoint, the Search function is not delegable. Therefore, it may not return accurate results if the size of your list exeeds the maximum of 2,000 records.

On balance, I agree with @poweractivate on the point that SharePoint better features and is more robust.