Unanswered

Filter with excel source

(0) Share

Report

Posted on by verbani

Hi,

I'm creating a PowerApp application with an excel as source. I have several dropdown boxes that I want to use to filter the gallery.

When I add one filter expression it works, But when I add a second I get an error that only one eq, ne, ... operation is allowed???

Any idea's on how to resolve this? I'm pretty sure that the syntax of the filter statement is correct, because I have it working in another application. But I can't get it to work with this application/excel combination.

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (1)

TheRobRush 11,128 Moderator on at

Like (1)

Report

I could be wrong, because I have not used Excel as a source for PowerApps for about two years. But we stopped using it because if two users tried to hit a button around the same time it would throw out errors in the same vein as this. I think once one signal has grabbed onto the excel source, it cannot be accessed by another until released. Sharepoint is superior to excel in this way. I'd get around that by collecting the excel into a collection on powerapps, having your app access it form there, and then returning the fresh data to excel as needed then recollecting with a clear collect. And maybe plan a move to sharepoint if your apps continue to get more complex.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Can you show us the filter statement you are using? You need to supply the full functions connect by an And/or. Or if you separate them with comma's the And connector is assumed.

Was this reply helpful? Yes No
eka24 20,923 on at

Like (0)

Report

It better you show the formula as well as the error to make your issue clearer

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Consider Checking My Youtube Channel

Was this reply helpful? Yes No
verbani 60 on at

Like (0)

Report

The code: Filter(TBL_Ondernemingsplan; Field = Drpdown1.Selected.Result; Field2 = Drpdown2.Selected.Result)

The error message:

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like (0)

Report
Made an Excel source to test a filter for you, mine is filtering properly when written as follows.

ClearCollect(Test,LookUp(CoachingTable,ASSIGNMENT="CAS11" && LOCATION="TICKETS",NAME))

Was this reply helpful? Yes No
Verified answer

timl 36,383 Super User 2025 Season 2 on at

Like (4)

Report
Hi @verbani

If you are using the Excel business connector, there is a limitation that means that you can only filter by one column. This is the reason for the "eq, ne, ... operation is allowed" error.

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/

The workaround is to nest your filter by calling the With function:

With({filterOne: Filter(TBL_Ondernemingsplan; Field = Drpdown1.Selected.Result)}, Filter(filterOne; Field2 = Drpdown2.Selected.Result) )

Alternatively, you could use the OneDrive/Excel connector, which supports the ability to filter by more than one column, but is limited to a 2,000 records, and a spreadsheet size of 2MB.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report
There is one problem with that as the solution. The documentation you cited is for Power Automate and Logic Apps, not Power Apps. Also, since it mentions operators like eq instead of = I think its talking about limitations on odata filters.

I've tested it in Power Apps and the following code works for me:

Filter(Datasourcename,Column1 = "Value" && Columnm2 = "Value2")

It does through a Delegation warning, but Excel that is because Excel isn't delegable.

Was this reply helpful? Yes No
verbani 60 on at

Like (0)

Report

Funny thing is dat Microsoft suggests this as an alternative themselves.
I do read the actual issue in the top of you screenshot. Microsoft Graph. This would also be used to read from excel PowerPivot tables, and is not supported.

I will be testing your solution shortly, but found that both suggest solutions worked for me.

But to be clear: I used the OneDrive for business solution. But when creating a connection to the table in the excel, the icon for the connection is the blue icon of OneDrive and not the green excel icon...

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report

@Pstork1 - Thanks - that's true and valid point you make about the documentation.

In my mind, I believe that the omission of Power Apps in the documentation is an error. From what I see, the limitations of the Excel business connector that are documented apply equally to Power Apps. For example, operations that filter records by more than one column (e.g., calling filter with multiple conditions separated by a comma), and similar functions, such as search return the same error message that only one column is supported. Also, if we attempt to call SortByColumns in Power Apps by more than one column, Power Apps returns an error about it not being able to sort by more than one column, which correlates to the connector limitation that is specified in the documentation.

=======================

To expand more on the 'With' pattern that I posted (with the green Excel for business connector), the Excel online business connector is delegable (with simple operators, that exclude searching against multiple columns).

With a test spreadsheet that I added with 40k rows, I can filter records that contain the city Chicago. This is a delegable expression, and the results include records that are interspersed throughout this 40k resultset. The records in the spreadsheet are sequential, and the PropertyID value correlates to the Excel row number.

On the premise that the Excel connector can only filter by a single column, the only way that Power Apps can execute a query that filters by multiple columns, is to execute the query locally in a non-delegable way. This would explain why the Power Apps shows a delegation warning when we issue a filter condition that combines multiple columns with the 'And' (ie the &&) operator.

With my test data, the And operator fails to the retrieve an expected record (record 5433) by postcode and city, which is expected because the formula displays a delegation warning.

By using the 'With' pattern, there is no delegation warning and the output returns the target record.

@verbani I've not tested this with the blue OneDrive for business connector (because it has traditionally not been delegable), but I'd be interested to see whether you can filter records with multiple columns in a delegable way with your setup.

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (0)

Report

@verbani

Just for info, I just tried to connect using the blue OneDrive for business connector, but the connection failed due to the size of the spreadsheet. I imagine that the 2MB/2,000 row limit that I mentioned in my first post still applies.

Therefore, if you're connecting to a spreadsheet with fewer than 2,000 rows, I'd agree with you that both the And and With solutions will work. However, I am slightly confused as to the error in your first post, because this should not occur with the 'blue' connector.

Was this reply helpful? Yes No