How to Get Distinct Values from SharePoint List using Power Automate?

If you want to get distinct values from a SharePoint list using Power Automate, check out this tutorial. Here, I have explained how to get distinct values from a SharePoint list using Power Automate based on one column as well as multiple columns.

Scenario:

We were using the SharePoint list to store the Product details, and we wanted to get the distinct values from a SharePoint list based on a single column as well as based on multiple columns.

We are using the below SharePoint list having the following columns.

Column NameColumn Type
ProductNamedefault title column
ProductIDText column
ProductPriceNumber column
get distinct values from SharePoint list using Power Automate

Here, I will show you two different examples to get distinct values from a SharePoint list using Power Automate based on one column as well as multiple columns.

In the first example, I will show you how to get distinct values for the above SharePoint list by using a single-column field called ProductName.

Similarly, In the second example, I am going to get distinct values presented in the list based on the ProductName and ProductID fields.

Now, we will start to create a flow to get distinct values from the SharePoint list using Power Automate:

How to get distinct values from SharePoint list using Power Automate

Let us see how to get distinct values from the SharePoint list using Power Automate Flow.

Example -1 : (Get Distinct Values based on One Column)

In this example, I will identify the duplicates based on the ProductName field presented in the SharePoint list.

So, in the following, we can see that the below Product SharePoint list has a repeated list of items with the same value for the ProductName (highlighted in red).

Now, we will start to create a flow to get only distinct items presented in the SharePoint list based on the ProductName field:

Step-1:

See also  How to Convert XML to CSV using Power Automate?

Create an instant cloud flow with a manual trigger, add a new step, select Get items action, and provide the required parameters:

  • Site Address – From the dropdown, select the site address
  • List Name – Select the list name (I have selected my list name called Products)
get unique values from array using power automate

Step-2:

Add a new step and choose select data operation; in the From section, Pass the value from dynamic content.

In the map section, enter the key name and map the values from the dynamic content. I have mapped the values of ProductName, ProductID, and ProductPrice as shown below:

How to get unique values from a Sharepoint column in Power Automate

Step-3:

Add a compose data operation, use the below union() expression, and pass the parameter as the output of the select data operation, which finds the distinct items based on the ProductName

union(body('Select'),body('Select'))
How to get distinct values from SharePoint list using Power Automate flow

Step-4:

Add a new step and choose the Create HTML Table action in the from section, pass the below expression:

outputs('Get_Distinct_Values')

In the columns, select the custom columns and pass the below-highlighted values:

To get the ProductName value, use the below expression:

item()['ProductName']

To get the ProductID value, use the below expression:

item()['ProductID']

To display the ProductPrice in the currency format, use the format-number function with C0 format:

formatNumber(item()['ProductPrice'],'C0')
get distinct values from SharePoint list in flow

Step-5:

Save and test the flow; once the flow runs successfully, like below:

Expand the Distinct Values table action to get only distinct values from the SharePoint list using Power Automate flow.

Power Automate get distinct list items from SharePoint list

This is how to get distinct values from the SharePoint list using Power Automate Flow.

Example -2: (Get Distinct Values based on multiple columns)

In this example, I am going to identify the duplicates based on the ProductName and ProductID columns presented in the SharePoint list.

By using flow, we will detect the duplicate items and get the distinct values presented in the SharePoint list.

Step:1

Create an instant cloud flow with a manual trigger, add a new step, select Get items action, and provide the required parameters:

  • Site Address – From the dropdown, select the site address
  • List Name – Select the list name (I have selected my list name called Products)
Power Automate get distinct values from sharepoint list

Step-2:

See also  Send an Email When an Item is Deleted in SharePoint via Power Automate

Add a new step and choose select data operation; in the From section, Pass the value from dynamic content.

In the map section, enter the key name and map the values from the dynamic content. Here we identify the duplicates based on the ProductName and ProductID presented in the SharePoint list.

I have used the below concat expression to concat the ProductName and ProductID

concat(item()?['Title'],item()?['ProductID'])

where,

  • Title – Column internal name for ProductName
  • ProductID – Column internal name for ProductID

Similarly, concat the values of the ProductName and ProductID and Created field to sort values based on the created date; for that, use the below expression.

concat(item()?['Title'],item()?['ProductID'],item()?['Created'])
get distinct values from SharePoint list in Power Automate flow

Step-3:

Add a compose data operation and use the below sort expression, which returns the values in ascending order based on the sort key used in the previous step (select data operation).

sort(body('Select'),'sortby')
get distinct values from SharePoint list in Power Automate

Step-4:

Add another select data operation in the from section, and use the below expression to find the list of items presented in the SharePoint list

range(0,length(outputs('Compose')))

In the Map section, switch the map to the text mode and use the below expression, where we are adding a property called IsDuplicate.

By using the if expression, I am checking if the contacted ProductName and ProductID with the remaining rows are presented in the SharePoint list.

If yes, it returns true, which means it returns the duplicate values in the SharePoint list. Else, it returns false, which means it returns the distinct values presented in the SharePoint list.

addProperty(outputs('Compose')?[item()],'IsDuplicate',if(equals(outputs('Compose')?[item()]?['concat'],outputs('Compose')?[sub(item(),1)]?['concat']),'T','F'))
Get Distinct Values Of SharePoint List Items in Power Automate

Step-5:

To get distinct values from the SharePoint list, add a new step and select filter array action; in the From section, use the below expression,

body('Select_2')

Filter the values; IsDuplicate is equal to False, which returns distinct values. For that, use the below expression:

item()?['IsDuplicate']
Get Distinct Values Of SharePoint List Items in Power Automate flow

Step-6:

Add a new step and choose the Create HTML Table action in the from section, pass the below expression:

body('Get_Distinct_Values')

In the columns, select the custom columns and pass the below-highlighted values:

See also  How To Use Power Apps Image Control [Detailed Guide]

To display the ProductPrice in the currency format, use the format-number function with C0 format:

formatNumber(item()['ProductPrice'],'C0')
get unique values of a Sharepoint column using Power Automate flow

Step-7:

To get duplicate values from the SharePoint list, add a new step and select filter array action; in the From section, use the below expression,

body('Select_2')

Filter the values; IsDuplicate is equal to True, which returns duplicate values. For that, use the below expression:

item()?['IsDuplicate']
Power Automate Get Distinct Values Of SharePoint List Items

Step-8:

Add a new step and choose the Create HTML Table action in the from section, pass the below expression:

body('Get_Distinct_Values')

In the columns, select the custom columns and pass the below-highlighted values:

To display the ProductPrice in the currency format, use the format-number function with C0 format:

formatNumber(item()['ProductPrice'],'C0')
get unique values of a Sharepoint column in Power Automate

Our complete flow looks like below:

Get Distinct Values Of SharePoint List Items Using Power Automate

Step-9:

Save and test the flow; once the flow runs successfully, like below:

Expand the Distinct Values table action to get only distinct values from the SharePoint list using Power Automate flow.

get distinct values from sharepoint list using flow

Similarly, to view the Duplicate items presented in the SharePoint list, expand the Duplicate Values table action as highlighted below:

Get Distinct Values Of SharePoint List Items using Power Automate flow

This is how to get distinct values from the SharePoint list using Power Automate Flow

Conclusion

I hope you got an idea to get distinct values from the SharePoint list using Power Automate flow.

Here, I have shown you how to get distinct values by detecting duplicate rows presented in the SharePoint list by contacting the ProductName and ProductID fields presented in the SharePoint list and also by using different functions presented in Power Automte flow.

You may also like:

>