Filter with excel source

[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.

[TheRobRush]

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.

[Pstork1]

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.

[eka24]

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

[verbani]

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

The error message: 

[TheRobRush]

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))

[timl · Accepted solution]

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.

[Pstork1]

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.

[verbani]

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...

[timl]

@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.