cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillPage
Solution Sage
Solution Sage

Filter a collection to exclude items

Hello.

 

This is theoretical so no code to post (sorry!). Say I have a collection that contains a bunch of records, and another collection that contains some, but not all, of the records that exist in the first collection.

 

How can I filter such that I only get the items that do not exist in both collections?

1 ACCEPTED SOLUTION

Accepted Solutions

@RusselThomas Thanks for taking time to investigate. Thankfully my requirements were's quite as complex, I only need to filter the first collection for things that aren't in the second, but not the other way around, which simplifies things massively. My collections are collections of records (is that called a table instead of collection?) but the theory is the same I think.

 

After I posted my question I had a bit of a hack around and came up with the quite simple formula:

 

Filter(collection1, Not(name in collection2).name))

Where name is a column that I can match between the two collections (whether or not the whole record matches column for column doesn't actually matter in this case).

View solution in original post

9 REPLIES 9
RusselThomas
Microsoft
Microsoft

Hi @WillPage ,

 

So let me see if I understand this - you want to see, of the two collections, which items are unique across both (i.e. "items that do not exist in both collections" but do exist in one of them). 

 

The alternative interpretation would be "items that do not exist in both collections" meaning a THIRD source of data where a record doesn't exist in either of the two collections  - but I don't see that here so let's assume the first 🙂

 

eg:

 

ClearCollect(myCollection1, ["Bob", "Sue", "Sally"])  //create collection and place the strings into the Value column

 

myCollection1.[Value] column

Bob

Sue

Sally

 

 

ClearCollect(myCollection2, ["Bob", "Paul", "Sally"])

myCollection2.[Value] column

 

Bob

Paul

Sally

 

Paul and Sue do not exist in both collections.

 

If my understanding is correct, then firstly, as each control can only contain a single source you can't really "Filter" both of these - you would need to create a third table with the results of your logic from both of these two tables and then show that.


As usual with PowerApps, there's probably ten different ways you can do this - here's two;

 

Button, Onselect: property;

 

Collect(myDeltaCollection, ["something"]); //just to initialise the collection with a Value column and text data type
Clear(myDeltaCollection);
ForAll(myCollection1, If(Not(Value in myCollection2.Value), Collect(myDeltaCollection, Value)));
ForAll(myCollection2, If(Not(Value in myCollection1.Value), Collect(myDeltaCollection, Value)))

you can then create a grid or gallery showing the myDeltaCollection items.

The "magic bean" here is the Not(Value in myCollection2.Value) statement.

 

Another option is to just plug this into the Items: property of a grid or gallery directly to avoid creating a third collection (but this is quite long-winded!);

 

Split( 
    Concatenate( 
        Concat( 
            Filter(
                myCollection1,
                Not(Value in myCollection2.Value) 
            ),
            Value, ";"),
        ";",
        Concat(
            Filter(
                myCollection2,
                Not(Value in myCollection1.Value)
            ),
            Value,";")
    ),
    ";"
)

Like I said, pretty long winded, but that's because this is an Items: property which is not actionable - meaning the result of whatever we put in here must be a Table.  No patching, forall'ing or collecting allowed 🙂

 

So here's what we're doing - let's work from the inside out as it makes more sense (to me anyway Man LOL);

 

Concat(filter collection1 where value doesn't exist in collection2.value, resulting column, ";") 

- gives us a ";" delimited string of all values in collection1 that don't exist in collection2.  This output would be "Sue"
Likewise;

 

Concat(filter collection2 where value doesn't exist in collection1.value, resulting column, ";")

 gives us a ";" delimited string of all values in collection2 that don't exist in collection1.  This output would be "Paul"

 

Concatenate(output1, ";", output2)

 - joins the two delimited string outputs of the two previous concat statements into a single delimited string - "Sue;Paul"

 

 

Split("delimited string", ";")

 - gives us a TABLE with column "Result" and each delimited value as a row in the table which we can use as Items: in a Gallery or Grid.  

[Result]

Sue

Paul

 

Like I said, there are probably easier or better ways to do this, and probably three different versions of those too.  Ultimately the approach would need to run the exclusion filter/logic across both original collections and output this in a third table.  I assume you'd get cirular reference errors if you tried to apply this logic to either of the two original collections themselves.

 

Hope this helps!

 

RT

 

 

 

 

 

 

@RusselThomas Thanks for taking time to investigate. Thankfully my requirements were's quite as complex, I only need to filter the first collection for things that aren't in the second, but not the other way around, which simplifies things massively. My collections are collections of records (is that called a table instead of collection?) but the theory is the same I think.

 

After I posted my question I had a bit of a hack around and came up with the quite simple formula:

 

Filter(collection1, Not(name in collection2).name))

Where name is a column that I can match between the two collections (whether or not the whole record matches column for column doesn't actually matter in this case).

v-xida-msft
Community Support
Community Support

HI @WillPage ,

Could you please share a bit more about your scenario?

Do you mean that the seocnd collection is a sub set of your first collection, and filter the first collection for things that aren't in the second one?

Further, have you solved your problem?

 

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround (set the OnSelect proeprty of a Button control to following😞

Clear(ResultCollection);
ForAll(
          Collection1,
          If(
              IsBlank(LookUp(Collection2, name = Collection1[@name])),
              Collect(
                            ResultCollection, 
                            {
                              name: Collection1[@name],
                              column2: Collection1[@column2],
                              column3: Collection1[@column3],
                              ...
                            }
              )
           )
)

Note: The name, column2, column3, ... are all columns in your Collection1.

After you press the Button, the unique records of your first collection would be populated within the ResultCollection.

Please take a try with above solution, then check if the issue is solved.

 

In addition, I also agree with your thought almost. Please modify your formula as below:

Filter(collection1, Not(name in collection2.name))

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Nice solution. I'm wondering if you know how to do the opposite and filter the first collection based what's IS in the second collection?

Hi @cmalm ,

 

If you're looking at the "in" operator then just remove the Not() function wrapping?

 

Items: 

Filter(collection1, name in collection2.name)

Hope this helps,


RT 

Thanks for the quick reply. One more question. What if I want to filter a sharepoint list with a collection of lookup field/records. I'm having issues with data types i.e. number vs record.

hi @cmalm ,


You'd probably have to be a bit more specific about your scenario - maybe start a new thread with a new question and some more details about your issue?  That way more people will see it (than just those following this thread :))

 

number vs record issues are usually a dot away 😉

 

Kind regards


RT

I took your advice and started a new more detailed thread. Please take a look.

https://powerusers.microsoft.com/t5/Building-Power-Apps/3-Source-Tables-Filtering-with-Foreign-Keys/...

PadmajaChandra
Frequent Visitor

Filter on date is still an issue even when we use collection. Any advice?

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,293)