Compare two arrays, tables or lists a lot faster in Power Automate

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.

Two arrays

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?

Reshaping arrays

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!

The results

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

Continue Reading Pieter Veenstra’s Article on their blog

Compare two arrays, tables or lists a lot faster in Power Automate

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.

Blog Syndicated with Pieter Veenstra’s Permission