You’re offline. This is a read only version of the page.
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.
Even i have the same issue. I have data source which is Excel Online and the data is more than 3000.
so firstly I create a variable set(colSource,ExcelData) and later inside my gallery item i used your fomula.
Filter(colSource,SHC_1 = "P1" && SHC_2 = "P2") this gives blank in my gallery
Filter(colSource,SHC_1 = "P1" && SHC_2 = "P2",Name) this too gives blank.
can you suggest me please, im stuck.
Hi @verbani
If you need to filter more than two fields, you would need to nest multiple with statements for each condition like so:
Although the syntax is not great, it should still return accurate results if you nest the with statements, starting with the filter condition that excludes the most records.
Initial post was with an excel connector. (Green icon)
later I changed this to the OneDrive.
I do have question about the With solution. In the example I gave, I only used 2 filters. But what if I need to filter more then 2 fields?
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.
@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.
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...
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:
Share
Report
All responses (
Answers (
Filter(Datasourcename,Column1 = "Value" && Columnm2 = "Value2")
It does through a Delegation warning, but Excel that is because Excel isn't delegable.
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.
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))
#1
WarrenBelz
254
Most Valuable Professional
#2
MS.Ragavendar
134
#3
stampcoin
123
