Filter collection using other collection

(0) Share

Report

Posted on by Sanu_Man

I have 2 collection as below

Table1

Col1	Col2
A,B,C	1
B,C,D	1
A,C,D	3
A,B	2
A,C	6
A,D	1
B,C	9
B,D	0
A,B,D	7

Table2

Col3

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

Col1	Col2	Col3	Col4	Col5
A,B,C	A	B	C	1
B,C,D	B	C	D	1
A,C,D	A	C	D	3
A,B	A	B		2
A,C	A	C		6
A,D	A	D		1
B,C	B	C		9
B,D	D	D		0
A,B,D	A	B	D	7

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?

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

Daniel Bocklandt 5,151 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
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.
Sanu_Man 5 on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Daniel Bocklandt 5,151 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
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.