Power Automate Expression Union – Return Unique Values

This blog will be looking at an expression called union() This expression can be used to remove duplicates from 2 collections. In this example, I will be using an Excel file to demonstrate this. But this can be done with any data

The Scenario

In this example, an Excel column contains repeated values.

The goal is to produce a list of unique values from that column. Why would we want to do this?
One reason may be that we get this file from a 3rd party, but we only care about the unique items to import into another system (CRM, CDS/Dynamics, SharePoint, Etc.)

If the workbook changes between runs, the separate dynamic Excel file and table walkthrough covers selecting the input. This example starts from the returned rows and builds a list of unique column values.

Steps

First we want to add an Initialize variable, Type = Array
Next, we need to get the data we want to use. In this example I am using List rows present in a table

Now we add a Apply to each loop, and use the dynamic content value from our Excel action
Inside the Loop we add a Append to array variable action, and add the column we want to remove duplicates from. In this example Paperitem

We are done inside the loop..

Outside the loop we add a Compose action, this is where we will put the expression union
In the Expression tab type union()
Select the dynamic content tab and pass the array variable to union twice
(Click your variable comma click variable again) Your expression should look something like this:

union(variables('paperItemArray'),variables('paperItemArray'))

Done!!

Final Thoughts

Now we have an array of unique values, which can be imported into another system based on your needs.

Thanks for reading!