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 Name	Column Type
ProductName	default title column
ProductID	Text column
ProductPrice	Number column

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:

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)

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:

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'))

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')

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.

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)

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. 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'])

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')

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'))

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']

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:

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

formatNumber(item()['ProductPrice'],'C0')

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']

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')

Our complete flow looks like below:

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.

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

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:

• Power Automate increment number in SharePoint list
• How to Save Email Attachment to SharePoint using Power Automate?
• How to Get File Content using Path in Power Automate
• Send an Email When an Item is Deleted in SharePoint via Power Automate
• How to Change Trigger in Power Automate