cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pwrappr01
Helper V
Helper V

How can I use ForAll and RemoveIf to remove items in one collection that are missing in another?

I have two collections I want to compare so I can ensure they match exactly. Here are samples of the two collections:

Collection1

IDItem
3C

 

Collection2

IDItem
1A
2B
3C


How can I write my ForAll( RemoveIf ) so it removes IDs 1 and 2 from Collection2 because they are missing from Collection1?

5 REPLIES 5
Rebetcha
MVP

Hi @pwrappr01 ,

 

I dont't think you need the ForAll in this case. The RemoveIf already allows you to loop through a table.

 

Why not do?: 

RemoveIf(Collection2, Not(ID in Collection1.ID))

 

pontusofsweden
Resolver IV
Resolver IV

Hi @pwrappr01 !

 

You dont need to use ForAll since RemoveIf loops through all lines anyway. Try this formula:

RemoveIf(
    Collection2,
    CountRows(
        Filter(
            Collection1,
            Id = Collection2[@Id] && Item = Collection2[@Item]
        )
    ) < 1
)

BR

Pontus

Thanks PontusofSweden

I had the same problem and I tried your suggestion... worked like a charm on the first 2000 rows.
Is there a solution without the delegation warning which I can use on all the rows?

Hi @FUS !

 

Do you really getting delegation warnings on Collections? You shouldn´t get that since it´s stored locally on your device. Or are you using another data source? I would always recommend to use Collections when working with the data in a Powerapp so if you are using Sharepoint for instance, try to fetch all the records to Collections on OnStart. It´s also good for the overall performance of the app 🙂

 

BR

Pontus

Yea, I'm using Sharepoint... but added a sort and in that way can do 4000 rows.
It's still a limitation, but couldn't get any other way to work. (Made a post about it)

Good suggestion about importing the data... will take it into consideration forward 🙂

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (903)