When you want to compare two arrays, lists or tables in Power Automate and you might end up with a very slow flow. In this post the speedy solution.
I’m going to create two arrays in compose actions. But most likely you would have a list in SharePoint or a table in Dataverse or maybe data coming from a completely different data source.
One important thing to notice is that the two arrays have a different structure.
Compare two arrays
When you now want to compare two arrays, you might think that two apply to each steps are needed. Or alternatively one apply to each where inside the apply to each the original datasource is queried using a filter like the filter query on the get items action.
This could then look something like this: Yuck!
The above is not just ugly, but also slow. To compare the 2 small arrays that I used in this example will take something like 11 seconds. And that is when not much happens inside the apply to each steps. When your flow does a lot more then things could really slow down. And all you want to do is compare two arrays.
Additionally, you will find that you might have to wait a long time before you can identify that the flow is actually failing as debugging nested flows can be hard.
So how can we speed this up?
The first step is to reshape the arrays a bit. At the moment we are comparing apples and pears. This reshaping of data is easily done with two select actions. Select actions are similar to apply to each steps, as they process all records in an array, but they are a lot faster as each item is processed with a single operation.
Now we can build an Apply to each step with a condition in it.
In this condition we can make use of the contains. Often contains is used to identify some text inside other texts, but this time I’m going to check if n object exists in my array.
So in the apply to each around this condition I’m selecting the output from the Select – Array1 reshaped action.
Then on the left hand side in the condition I’m selecting the output from my second select action. As an operation I’m going for contains and on the right hand side I’m selecting the current item of my apply toe each step.
That should now all look like this:
Time to put this to the test!
When I run my flow, it takes just 1 second, to process the 3 and 2 items. Ok, I’m only using small amounts of data. But compared to the nested apply to each solution this is pretty fast!
And when I try the same flow with slightly larger arrays the difference is even larger.
With one array of 10 objects and another array with 5 objects, I’m finding
So why are those select actions so much more efficient, especially with larger mounts of data?
In my first example with the nested apply to each steps, each record is processed. So even though, the first record found is the item that we were looking for, apply to each steps will continue to process the rest of the array. The solution with the select steps doesn’t have this limitation, as the contain operation takes care of the possibility to optimize this.
In this post I mentioned one of the ways to avoid nested apply to each steps. If this pattern doesn’t fit your nested apply to each flowm, then you migth also want to have a look at:
Avoid unwanted apply to each steps in Power Automate
Nested arrays with a single item in Power Automate
7 thoughts on “Compare two arrays, tables or lists a lot faster in Power Automate”
Good stuff! I’ve had success using expression functions for Azure Logic Apps and Power Automate. When dealing with arrays of objects, these functions quickly compare various data types and either return the matches or return the differences. In a Filter array activity, I click “Edit in advanced mode”, which lets me designate multiple criteria for comparing objects and values. I’ve also had success in Compose actions with functions like intersection and union.
Based on this method, i have another way. Compare with string
String(arrayA) contain ” items(‘Apply_to_each_5’)?[‘ID’] “
I have two arrays. One array has 3 columns and the other one has one. Will I still be able to compare it using the way you mentioned here ??
How can this be achieved??
After reading the post how far did you get?
Hi, Good explanation thanks. At first the condition didn’t work for me and then I realized it was because the two SELECT’s had different names for the column. In your example you have both columns in the SELECT named ‘mytext’ whereas I had the with two different names to make it easier to identify them, but then the condition wont work. They columns have to have the same name for the contains to produce a true.