Skip to main content

Notifications

Community site session details

Community site session details

Session Id : HXUGf3VKWoQK/jBgvNw/se
Power Apps - Building Power Apps
Answered

Filter with excel source

Like (0) ShareShare
ReportReport
Posted on 9 May 2021 15:15:10 by 60

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.

 

 

 

  • pchettri Profile Picture
    39 on 03 Oct 2023 at 12:21:10
    Re: Filter with excel source

    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.

  • timl Profile Picture
    34,978 Super User 2025 Season 1 on 10 May 2021 at 12:09:35
    Re: Filter with excel source

    Hi @verbani 

    If you need to filter more than two fields, you would need to nest multiple with statements for each condition like so:

    timl_0-1620648409352.png

    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.

  • verbani Profile Picture
    60 on 10 May 2021 at 10:55:52
    Re: Filter with excel source

    @timl

     

    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?

     

     

  • timl Profile Picture
    34,978 Super User 2025 Season 1 on 10 May 2021 at 10:07:22
    Re: Filter with excel source

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

  • timl Profile Picture
    34,978 Super User 2025 Season 1 on 10 May 2021 at 09:47:16
    Re: Filter with excel source

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

  • verbani Profile Picture
    60 on 10 May 2021 at 05:36:38
    Re: Filter with excel source

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

  • Pstork1 Profile Picture
    66,091 Most Valuable Professional on 09 May 2021 at 19:52:35
    Re: Filter with excel source

    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.

  • Verified answer
    timl Profile Picture
    34,978 Super User 2025 Season 1 on 09 May 2021 at 16:36:52
    Re: Filter with excel source

    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.

     

     

  • TheRobRush Profile Picture
    11,121 Super User 2025 Season 1 on 09 May 2021 at 16:13:22
    Re: Filter with excel source

    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))
  • verbani Profile Picture
    60 on 09 May 2021 at 15:53:10
    Re: Filter with excel source

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

     

    The error message: 

    verbani_0-1620575519267.png

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,745 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard