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