cancel
Showing results for
Did you mean:
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
Solution Sage

@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).

9 REPLIES 9
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 );

`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

Solution Sage

@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).

Community Support

HI @WillPage ,

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.

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

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.
Helper II

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?

Microsoft

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

Helper II

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.

Microsoft

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

Helper II

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

Frequent Visitor

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

Announcements

#### Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

#### Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

#### Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,828)