cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Imke
Advocate III
Advocate III

How to do an anti join?

Hi there,

I have 2 arrays and want to keep only those items from the first array, which are not included in the second array:

 

first array: [1, 2, 3]
second array: [3,4]

desired result: [1, 2]

Any help appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
DamoBird365
Super User
Super User

Hi @Imke 

 

@Paulie78 thanks for the shout out!  

 

I've just written a blog post on exactly that, it's like you knew!  You can take a look here where I cover, Union, Except and Intersect.  It sounds like in this case you are looking to perform an Except and return only the items that are unique to the first array.  As there is no in-built expression for this you can use a select action and compare your first array with your second with each item, all in one action! Alternatively you can use an apply to each on the first array and systematically work through the second array checking to see if a value exists, this is rather time consuming and a significant overhead on your Cloud Flow.

 

Here is an example of an except where list2 items don't appear in list1 (which is simply a reverse of your requirement).  Please take a look at my blog post and video and let me know how you get on.

 

DamoBird365_0-1616342945888.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

View solution in original post

6 REPLIES 6
Paulie78
Super User
Super User

@DamoBird365 has a great method for this....

Imke
Advocate III
Advocate III

Cool, thanks for calling him in.
Just wanted to add that the number of items (both to keep and to skip) is variable.

 

DamoBird365
Super User
Super User

Hi @Imke 

 

@Paulie78 thanks for the shout out!  

 

I've just written a blog post on exactly that, it's like you knew!  You can take a look here where I cover, Union, Except and Intersect.  It sounds like in this case you are looking to perform an Except and return only the items that are unique to the first array.  As there is no in-built expression for this you can use a select action and compare your first array with your second with each item, all in one action! Alternatively you can use an apply to each on the first array and systematically work through the second array checking to see if a value exists, this is rather time consuming and a significant overhead on your Cloud Flow.

 

Here is an example of an except where list2 items don't appear in list1 (which is simply a reverse of your requirement).  Please take a look at my blog post and video and let me know how you get on.

 

DamoBird365_0-1616342945888.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

Ha, ha - yes, that's almost what I just tried: 
@not(contains(outputs('Array2'), item()))
Thanks!!

Imke
Advocate III
Advocate III

Your article was probably too new to be picked up by my search machine 🙂
Very good!

DamoBird365
Super User
Super User

Hi @Imke 

I had to look at your profile join date as I couldn't believe that this had popped up today.  Very amusing and grateful to have the opportunity to help you.

Cheers,

Damien

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (3,786)