cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
verbani
Helper I
Helper I

Filter with excel source

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User III
Super User III

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/

 

timl_0-1620578060609.png


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.

 

 

View solution in original post

12 REPLIES 12
TheRobRush
Impactful Individual
Impactful Individual

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.

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
eka24
Super User III
Super User III

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

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

 

The error message: 

verbani_0-1620575519267.png

 

TheRobRush
Impactful Individual
Impactful Individual

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))
_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
timl
Super User III
Super User III

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/

 

timl_0-1620578060609.png


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.

 

 

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

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.

image.png

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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
Super User III
Super User III

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

timl_0-1620639247459.png

 

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.

timl_1-1620639356505.png

 

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.

timl_2-1620639443245.png

 

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (49,003)