web
Youโ€™re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : 4cQ+UZ2zQT2/PEUn/Gd2Gp
Power Apps - Building Power Apps
Answered

Filter with multiple conditions using Excel as data source

Like (0) ShareShare
ReportReport
Posted on 6 Oct 2022 04:12:42 by 14

I have a simple Excel Spreadsheet as my source data, and a DataTable control on a Canvas app. I am having trouble filtering with two conditions. I see many posts on this and they look quite easy to follow, but I am not seeing what I am doing wrong here, because when I apply two conditions, the table shows "we didn't find any data to show at this time".

 

My excel spreadsheet is as below (with just two rows in the sheet):

 

Column1Column2
aa
ab

 

When I filter with one condition I get two rows returned which is correct.

Filter(Table1, Column1 = "a")

 

If I change this (still with one condition) to filter on Column 2 I get one row which is also correct.

Filter(Table1, Column2 = "a")

 

However if I combine these two conditions together on the filter it does not return any rows at all.

Filter(Table1, Column1 = "a", Column2 = "a")

 

Can you please help me understand what I am doing wrong here?

 

David_Morris_0-1665029458772.png

 

David_Morris_1-1665029469308.png

 

David_Morris_2-1665029479166.png

 

 

  • BlessedCobba Profile Picture
    459 Super User 2024 Season 1 on 09 Oct 2022 at 23:43:01
    Re: Filter with multiple conditions using Excel as data source

    @David_Morris 

    A general rule is excel should only be used as a data source for learning how to PowerApps. if you're going to be making an app for use then at a minimum you should be using SharePoint as a data source

    you can export / connect to a SharePoint list from excel if people need to use the data in that way but should never really be the basis of a power app data source

  • Verified answer
    David_Morris Profile Picture
    14 on 06 Oct 2022 at 05:17:47
    Re: Filter with multiple conditions

    Thanks @BlessedCobba , using "&& has worked, however that presents a delegation warning which is not optimal ๐Ÿ˜ž

     

    I tried myself with a collection and can confirm it works.

    Collect(Table2,{Column1:"a",Column2:"a"},{Column1:"a",Column2:"b"});

     

    And the original filter command I was using works and returns one row correctly.

    Filter(Table2,Column1="a", Column2="a")

     

    When swapping back to the excel data source as per the original post, I cannot filter on both Column1 and Column2 at the same time using the above filter statement (which is delegable). While using the "&&" has worked, it is not delegable and therefore not optimal.

     

    I will mark your response as an accepted solution given it has in fact worked per the original post, but given the solution throws a delegation warning, I would love to hear if there are any other answers worth trying.

  • BlessedCobba Profile Picture
    459 Super User 2024 Season 1 on 06 Oct 2022 at 04:46:21
    Re: Filter with multiple conditions

    @David_Morris 
    My Apologies, when doing a filter it uses a && 

    BlessedCobba_2-1665031533104.png

     

     

    You are correct that a comma can be used (i was not aware), I have just always used && instead

    Not sure why yours is returning no results, I have tried it both ways and it seems to work, my only guess is something to do with the data source, i just used a test collection for mine

     

  • David_Morris Profile Picture
    14 on 06 Oct 2022 at 04:26:53
    Re: Filter with multiple conditions

    Thanks for the response @BlessedCobba , but I don't believe that is the right solution unfortunately.

     

    The help docs on the Filter command suggests the below, which shows a comma separated list of all logical tests like what I had in the original post.

    Filter(Source, logical_test, logical_test, โ€ฆ)

     

    However, I tried your suggestion anyway, which shows a delegation warning on the "=" and "&" portions of the condition, and also does not return any records.

    David_Morris_0-1665030341957.png

     

     

     

     

  • BlessedCobba Profile Picture
    459 Super User 2024 Season 1 on 06 Oct 2022 at 04:19:11
    Re: Filter with multiple conditions

    Hi @David_Morris 

    you will want to use an & symbol instead of the , if you want both conditions to be true

    if you want it for either conditions then you will use OR 

     

    Filter(Table1, Column1 = "a" & Column2 = "a")
    
    Filter(Table1, Column1 = "a" OR Column2 = "a")

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

Telen Wang โ€“ Community Spotlight

We are honored to recognize Telen Wang as our August 2025 Communityโ€ฆ

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful forโ€ฆ

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Loading complete