Compare two arrays, tables or lists a lot faster in Power Automate Microsoft Power Automate apples and pears

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.

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

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!

Compare two arrays, tables or lists a lot faster in Power Automate Microsoft Power Automate Slow flow nestedapply to each steops

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.

Compare two arrays, tables or lists a lot faster in Power Automate Microsoft Power Automate Nested Apply to each

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.

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

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.

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

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:

A flow to Compare two arrays

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!

Compare two arrays, tables or lists a lot faster in Power Automate Microsoft Power Automate Single apply to each

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

Compare two arrays, tables or lists a lot faster in Power Automate Microsoft Power Automate image 35
And when I increase that to 20 and 10 records, the difference is increasing once again:
Compare two arrays, tables or lists a lot faster in Power Automate Microsoft Power Automate image 36

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

Avatar for Pieter Veenstra

By Pieter Veenstra

Business Applications Microsoft MVP working as a Principal Architect at HybrIT Services Ltd. You can contact me using contact@sharepains.com

9 thoughts on “Compare two arrays, tables or lists a lot faster in Power Automate”
  1. 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.

  2. Based on this method, i have another way. Compare with string

    String(arrayA) contain ” items(‘Apply_to_each_5’)?[‘ID’] “

  3. 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 ??

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

  5. Hi there, apologies, very new to Power Automate. How do you get the title and the subject. It appears like it might be a compose function, but when I’ve tried this I’m unable to get it?

    1. Hi Amit,

      Which title/subject do you want to get? Can you give me some details?

      In general you can get to data delivered by previous action by using either dynamic data or to write expression querying the json from the previous actions.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from SharePains by Microsoft MVP Pieter Veenstra

Subscribe now to keep reading and get access to the full archive.

Continue Reading

%d bloggers like this: