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 the elusive EXCEPT without using an Apply to Each.
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.
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.
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.
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.
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.
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
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.
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.
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).
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!
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
Very good video! Can you please tell me how you created the output for the Extract. I have 1 SharePoint list with List Programs containing a list of Program names and a second for a List of Projects that have Programs assigned. The Program list Title contains some items that are not in the Project list field Name (Title was renamed).
So when I run
From Programs
Projects – does not contain – I created item()
However when I run the Automate I just get the same Output as the Input and I can’t figure out why the “does not contain” isn’t filtering. All the values.
Thoughts
I am running into the same issue. Did you find a solution?
This worked fine for me when I built a test version with arrays created directly in compose. It does the same as Brian’s when I did it with arrays created from SharePoint.
I’ve got an updated version of the article here https://pnp.github.io/blog/post/comparing-two-arrays-without-an-apply-to-each I am curious to learn what problem you are encountering? You will need to repurpose the array using the select in text mode but then you should be able to compare using the filter and condition.
Good and it help me