Finding the differences between to arrays of objects is a common task that you may need to perform in your Power Automate workflows but you may be surprised that it isn’t as easy as you would expect, especially when you try to do it at scale on arrays with thousands of items in them.

Select - format spItems for ComparisonSelect - format spItems for Comparison

The magic behind this Power Automate flow is the intersection function. This function will compare x number of arrays and return all the common items across them. The beauty behind this function is that if you provide it arrays of objects and the objects have the same schema it will do the comparison check against all properties on the objects. Oh and did I mention it is fast, in testing the comparison on 2 arrays of around ~30,000 object with about 7 properties each it completed in 2 seconds.

Compose - intersection of spItems and csvCompose - intersection of spItems and csv

The reason why we find the common items in the arrays is so that we can remove them with a Filter array action. You may also be wondering why we simply don’t just perform the Filter array action on the items themselves and the answer is simply due to performance. The objects you have in an array and the more properties you have on those objects the longer it would take to filter an array for just the differences. In testing I was noticing the Filter array action taking 7-8 minutes to complete which was 3 minutes longer than it took my whole flow to finish.

Now the key to speeding up the Filter Array action is to not perform it on an array of objects but rather an array of string or integer. This means that your objects are going to need a primary key to do the testing against, for me the primary key was actually in the Title property so it was easy. If you do not have a primary key then you will need to get creative and maybe string together the properties in the object to make a unique key.

To create the array items we will use the Select action within Power Automate to create an array of just Titles and do this for both of the arrays that we are comparing as well as the array returned from the intersection method.

Select - intersection (Titles Only)Select - intersection (Titles Only)

Once you simplified your arrays you can perform the filter action on each array you compared to remove the common items between each of them and you will be left with only the differences. To help speed up the overall Flow we can perform the Filter array actions in parallel branches.

Filter the arraysFilter the arrays

Our Filter array actions leverage the contains function to check to see if an item is present in a given array. In our example the From in each action is the output from the Select – spItems (Titles Only) and the Select – csv (Title Only) actions respectively. The left hand side of our check is a contains function where we are passing the output of the Select – intersection (Titles Only) action as the array and using the item function to reference the current item to see if it is in the array. If it is then the contains function will return true. Our right hand side of the check is false because we only want items that are not common to both arrays, aka the differences.

Filter the arrays outputFilter the arrays output

You can see the speed, ~1 minute each, at which the Filter array functions complete and the outputs. Hard to show in the screenshots but the inputs contain ~30000 items for each array and the outputs are the differences. You may notice similarities in the outputs but that is because we are simply looking at the Title (primary key) and the differences on the objects may be in other properties on the object.

Now that we have arrays of the differences we simply need to use the Filter array action on the original data sources to get an array of all the differences between the arrays but with the objects and all their properties.

Filter array - full itemFilter array - full item

Again here we use the contains function but looking for where it is true. This Filter array action will return an array of objects.

Filter array - full item outputFilter array - full item output

Depending on your business logic or scenario you may actually be able to streamline the process and handle this in the initial filter but after finding the differences between the arrays I had more business logic I had to run the arrays through and wanted to performance gain of doing so with smaller arrays.

The beauty of this lies in the application because it allows you to do comparisons on large data sets relatively quickly. You could use this method to compare 2 datasets to see what has changed and then take action upon those changes, for instance keeping a SharePoint list up-to-date from an external data source or file such as a CSV. Doing this comparison would allow you to only make changes needed versus resetting it back to source by deleting everything in the list and adding back all the items from the CSV.

AaaS - Anthony as a Service