One of the challenges you might face in Power Automate or Logic Apps is comparing the contents of two arrays. The arrays could come from anywhere such as SQL, SharePoint, Excel or a multiple choice question in a form etc..
Like with anything, there are multiple ways to skin the proverbial cat and my goal in any flow or logic app I build is to make it as efficient as possible. That is to say I try to eliminate loop actions whenever possible and minimise the use of variables.
With that goal in mind, here is my way of comparing two collections and presenting the result in an HTML table without any loops or variables
Firstly, lets define a couple of collections.
These two tables in Excel are called Collection1 and Collection2 and the columns have the same name, except the space character in the column name does not exist in the table name.
Now we want to drop any other columns besides the data we’re interested in from the collection using a Select action for each of them. Excel has a couple of system columns we’re not interested in, @odata.etag and ItemInternalId that we want to get rid of to produce a single column array
Here is the output:
Now we need to build a full list of all the items in the two collections together. For this we draw on the magic power of the union() function. By using union to combine these two collection we build a full list without duplicates. Yes indeed dear reader, union() can be used to dedupe an array by “combining” an array with itself, but we’re not doing that here.
The result:
Now lets filter this list for items that are contained within collection 1 and not within collection 2. This gives us the items only in collection 1:
The output:
The same goes for collection 2, just copy and paste the action and swap 1 and 2 around in the formula.
Fruit in both collections is contains() for both:
So, now we have 3 collections. Things that are only in collection 1, things that are only in collection 2 and things that are in both collections.
This might be all you need for your solution, but we can do a bit more transformation to recombine these 3 collections.
Let’s use a Select to add a column to each array:
Each of these Selects are adding a column with the same name (this is important), which I’ve called Collection and put a static string value in each to indicate which collection the items come from
Next we use union() again to combine the arrays:
The output:
You can then create an HTML table with the output or do whatever you want with it
With no loops and variables, all this happens in 50ms
If you like my work, please feel free to donate
William's IT Blog 