Answered

Filter collection based on presence of similar item in another collection

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi All

I have 2 collections from 2 SP lists with same columns and I need Collection1 to have only items that are not present in Collection2.

Anybody knows how this can be done?

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@Anonymous
You must loop through Collection1 using FORALL to check if each record exists in Collection2 with the help of the LOOKUP functon. My assumptions are Collection1 initially has all of the records stored within it and we are seeking to remove any duplicates. I also assume your collection has only a single column called your_column.

ClearCollect( Collection1, ForAll( Collection1, If( IsBlank(LookUp(Collection2, your_column_2, Collection1[your_column_1])), {your_column_1: your_column_1} ) ) )

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Both lists/collections have a lots of same columns

Was this reply helpful? Yes No
v-monli-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

In addition to what @mdevaney has suggested, if your collections' columns are more than 1, please first use below formula to create a new collection with only one column:

ClearCollect(Collection3,Distinct(Collection2, NEWCOLUMN))

Then use this Collection3 to loop through Collection1.

Regards,

Mona

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Thanks for your suggestions guys, but I think I found better way to do it:

ForAll(Collection1, If( Column1 > 0 , RemoveIf(Collection2, Column1=Collection1[@Column1]) ) );
Let me know what you think

Was this reply helpful? Yes No