Power Automate | Efficiently perform Union, Except and Intersect

Imagine you have two datasets that you want to compare where you would like to include or exclude common or uncommon rows from each? Out of the box, the Power Automate platform allows you to compare datasets with the Union and Intersect expressions. You might require all members of staff to complete a form and as they do so, as well as capturing their results to an excel file or list, you also capture their email. Using a seperate list of all users, maybe an O365 group, you routinely email a reminder to those that are yet to respond, this would be an EXCEPT i.e. users in the group list that don’t exist in the list of submissions. Below I explain how to perform these actions efficiently and perform elusive EXCEPT without using an Apply to Each.

Union Insersect Except
Image from EssentialSQL.com
Watch my demonstration or continue reading below

I have prepared an excel table with two columns in order to demonstrate the 3 scenarios. Your data source(s) could of course be a column list or other data source available to Power Automate. In NameList1, I have a list of 12 male names and in Namelist2, I have 6 of the same male names, followed by 6 female names. I have colour coded these to provide a visual representation.

Table in Excel

To prepare my two datasets, I have used the select action to create an array of both columns of data. I take the values returned by the Get Table action and then Map the Nameslist1 and 2 to seperate Compose Actions. You can map the select to an individual element of the source by clicking on the “Switch Map to text mode” option as highlighted. I was recently taught this trick by Paul Murana of Tachytelic. This replaces the often used sets of actions, Initialise Array Variable and Apply to Each with an Append to Variable for each Current Item. The single action is therefore far more efficient.

Gathering the data
The output from the Select Action – a neat Array

Once you have prepared your two datasets as two seperate arrays using the Select Action, you are able to begin comparing the data as follows.

UNION

The union is relatively straighforward. There is an out of the box expression Union which you can use to compare the two arrays. The outcome of which is a list of ALL names from BOTH lists.

Union Expression
Output from the Expression

EXCEPT

This is the exciting one for me as there isn’t an out of the box EXCEPT expression. An Except clause will return the elements of the first Array that DO NOT APPEAR in the second Array. Quite often a solution is built using an Apply to Each action which carries an overhead, expecially when comparing large lists. The solution would require a seperate variable for storing the results of a condition action. If the current item is in the array, append it to the variable, otherwise do nothing. Here I demonstrate how to perform this clause in one simple step using the filter array action.

The results of the Filter Array Action show that only List2 Items that are unique are returned by the Output as an Array

So how is this done? Using the Filter Array Action and the List2 Select Body as the From Source, we can check the the List 1 Select Body Source does not contain the Item from List2. In a single action we have performed an EXCEPT.

Single Action Except Clause using Filter Array Action

I tested the performance of this select action on ~3500 UK Baby names compared against 1000 US Baby names and the Excel Action alone took 20 seconds to list all rows. The Except Select Action finished in 0s. I built the Appy to Each equivalent and tested same and it timed out after 10 minutes so I turned on concurrency. The Action eventually completed in 2 minutes

Except Apply to Each equivalent

During testing of larger and dissimilar sized lists I discovered that the filter array was returning null values. I was able to resolve this by editing the condition in advanced mode and including a check to ensure that the item being compared was not empty.

@and(not(contains(body(‘List1’), item())),not(empty(item())))

INTERSECT

Much like the union, the intersect is relatively straighforward. There is an out of the box expression Intercept which you can use to compare the two arrays. The outcome of which is a list of ALL names that appear in BOTH lists but are not unique to either.

Intersect Expression
Output from the expression – the 6 common boys names between the two lists

Have you needed to do a data comparison similar to this in Power Automate? What solution did you go for? Could this save your flow unnecessary actions and increase the efficiency of your Cloud Flows? What types of data are you comparing?

Please let me know below or get in touch!

Want to read about another efficient use of the select action? How to avoid another Apply To Each action? Take a look at my article on Parsing a CSV File into a JSON Array (click here).

2 thoughts on “Power Automate | Efficiently perform Union, Except and Intersect

  1. Thanks for this, the Except is really the greatest thing here!
    I will have to test it out. Defo bookmarked! 🙂
    One other thing that you can add, is that if you just want unqiue elements from an array, you can do union(Array1,Array1) and it will return just the unqiue elements from Array1 removing any duplicates!

    1. Hey Jamal, absolutely. I have recommended the same action on the Community Platform recently in order to get a list of unique values from a list. Thank you for highlighting that idea! It’s a little known trick and again a really efficient way of comparing data. Have a great day! Damien

Leave a Reply

Your email address will not be published. Required fields are marked *

Please reload

Please Wait