How to get distinct records using the Union function in Power Automate

The Union function in Power Automate is used to return a collection that contains all the items from the specified collections/arrays.

For example the following function

union(createArray(1, 2, 3), createArray(1, 2, 10, 101))

will return the following array [1, 2, 3, 10, 101]

Currently in Power Automate there is not a "Distinct" action or function that returns unique records from a data-source. Luckily using the union function against the same array you can achieve the same result.

For example if you have a SharePoint list containing the following records:

In Power Automate you can use the union function to get the distinct records of the cities.

Here is how:

1. Initiate a variable of type Array (the viewArray variable is used only to show the values of the CitiesArray) and Get Items from the SharePoint List.
   
2. Append the Title of each records in the SharePoint to the CitiesArray variable
   
3. As you can expect the array contains all the values that are in the list
   
4. To remove the duplicate values from the array so it shows only the distinct records, (like a distinct command in SQL), use the union function as shown below
   

   union(variables('CitiesArray'),variables('CitiesArray'))​

   
   
   The array will now contain only distinct records
   

About the author

Giorgio Capisani

I am an experienced IT professional specialising in Microsoft technologies (SharePoint 2010/2013/Online, Power Platform, Office 365), with over 10 years of experience in large public and private sector organisations.