Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

Filter collection using other collection

Posted on by 5

I have 2 collection as below

 

Table1

Col1Col2
A,B,C1
B,C,D1
A,C,D3
A,B2
A,C6
A,D1
B,C9
B,D0
A,B,D7

 

Table2

Col3
A
B
D

 

I am trying to match the item of Table2.Col3 with Table1.Col1 and get Table1.Col2 in return ( expecting 7 in return for A,B,D combination from last row of Table1)

 

I did try the below below formula but it is not working

Filter(Table1, Col1 in Table2.Col3)

 

As an alternative, I did reform Table1 as below, kept Table2 as before

Col1Col2Col3Col4Col5
A,B,CABC1
B,C,DBCD1
A,C,DACD3
A,BAB 2
A,CAC 6
A,DAD 1
B,CBC 9
B,DDD 0
A,B,DABD7

 

used the below formula

Filter(Table1, (Col2 = Blank() || Col2 in Table2), (Col3 = Blank() || Col3 in Table2), (Col4 = Blank() || Col4 in Table2)).Col5

 

What this is doing is, it is returning all the rows where part of the items are there ( getting back data from Table1.Col5 for A,B/A,D/B,D/A,B,D in Table1.Col1)

 

 

Any help?

  • DBO_DV Profile Picture
    DBO_DV 4,116 on at
    Re: Filter collection using other collection

    I worked something out. Not the most beautiful solution there is probably but it should do the job: 

    With(
     {RowNumber: CountRows(Table2)},
     Filter(
     Table1,
     And(
     If(
     RowNumber > 0,
     Col2 in Table2
     IsBlank(Col2)
     ),
     If(
     RowNumber > 1,
     Col3 in Table2,
     IsBlank(Col3)
     ),
     If(
     RowNumber > 2,
     Col4 in Table2,
     IsBlank(Col4)
     )
     )
     )
    )

     

    Hope this helps you out.

     

  • Sanu_Man Profile Picture
    Sanu_Man 5 on at
    Re: Filter collection using other collection

    I tried it and it worked. Problem is that Table1 can have A,B,D/A,D,B/D,B,A/B,D,A, in short any combination. Hence even if I sort Table2, there's no way for me to know if combinations in Table 1 was sorted before doing the concatenation.

  • DBO_DV Profile Picture
    DBO_DV 4,116 on at
    Re: Filter collection using other collection

    Hey @Sanu_Man ,

     

    If the Format is always like this you could use this function:

     

    Filter(
     Table1,
     Concat(
     Table2,
     Col3,
     ","
     ) = Col1
    )

     

    Just make sure to always sort your tables accordingly.

     

    Let me Know if this solution worked for you.

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

October 2024 Newsletter…

October 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #4 How to Conntact Support…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 142,651

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,727

Leaderboard