“I have SharePoint lists with users and registrations, how can I compare the lists (arrays), to see if everyone is registered using Power Automate?”
Comparing two lists, let it be SharePoint lists or any other, is quite an annoying task to do, especially for bigger lists. Since Power Automate doesn’t have any function to compare arrays (and lists are just arrays) you have to implement such functionality by yourself. And your first attempt might be to loop through both the lists and compare it item by item – a performance nightmare…
What’s the ‘better’ solution then? How can you compare two lists, two arrays, and find the differences in an efficient way?
Create the two arrays for comparison
To find the missing values you need two inputs – the array with all the values, and the second array with some of them. For example a list with all users and a registration list.
The first step is therefore to get all the data into Power Automate flow. ‘Get items’ from the first list and ‘Select’ the values to compare, in this case the email addresses.
Repeat the same steps for the second list, with the registrations.
The outputs of the ‘Select’ actions will be the two arrays to compare.
List the missing values
Since Power Automate doesn’t have an expression to find the differences, you’ll have to do a small workaround.
Instead of an expression use the ‘Filter array’ action. Use the bigger array as the From value, the smaller array on the left side and the item() expression on the right side.
The result will be an array with the values from the first array missing in the second array.
List whole SharePoint items
The example above compared two simple arrays, but that’s not always the case. Often you’re comparing objects in an array, e.g. items in two SharePoint lists. In such case it wouldn’t make sense to select only the emails for comparison as you want the whole items.
Instead of selecting only the value to compare from the first list, take the whole list. And since you’re now referencing the whole list, you can’t use simply item() – must reference the specific property with an expression.
Similarly to the example above, I’m looking for items in ‘Users’ where the user is not in the ‘Registered’ users list, still searching by the email.
As I’m now filtering in SharePoint items, this time I’ll get the whole items instead of just email addresses.
Summary
There’s currently no expression in Power Automate to compare two arrays and find the differences, but there’s again a workaround. Get both the arrays into your flow and let the ‘Filter array’ do the work. No loops, no comparing it item by item, a single action that’ll do the filtering for you.
This example was based on a SharePoint list, but it doesn’t stop there. You can use the same approach to compare any arrays, let it be single arrays or arrays with object, this solution will do the trick.
I believe I am following the example; however, I am not receiving the values missing in the second array (for my data).
This is amazing! You rock!
I’ve gotten this far. What I’m wondering is how would I compare vs Employee ID instead, then send an email to folk not on the missing list.
Hello El Baz,
use ‘contains’ or ‘does not contain’ depending if you need people ‘in’ the list or ‘not in’ the list.
Brilliant. This is exactly what I’m looking for!
Power Automate change @not(contains(ArrayA,item()) to @not(notcontains(ArrayA,item()). Why?
Does this still work? I can’t get it to.. there seems to have been a change with it. The “SELECT” now as a different map..
Hello Dan,
yes, I’ve been using this on regular basis.