I want to preface this by saying that this example will use a small list of users. If you are dealing with a “large” SharePoint list, Excel file, table, or API call with more than ~5,000 rows, this post is not for you. Yes, there are ways around this, but this post is a super simple example to get a unique list of users from a data source.

In this post, I’ll show you how to use a Power Automate Flow to get a unique list of users from a SharePoint list or an Excel file. In both examples, setting up the data should be simple. For the SharePoint list example, you will need access to a site where you can create or modify a list, or at a minimum, access to a list with a person column. For the Excel example, you will need access to OneDrive; if not, the same basic steps will work if you have access to a SharePoint site that has an Excel file in a library.

SharePoint list example:
First, let’s set up the SharePoint list. From a SharePoint site, click the gear icon in the top-right corner, then select Site contents. From the Site Contents page, click on + New and select List.

Depending on when you read this, the following screen might look different than my screenshot. From the window that opens, click on List. Name the list: MyTestList and click the Create button.

Do the following in the newly created list:
Click + Add column –> Person –> Next
In the Create a column window, name it Approver and click Save.


In the SharePoint list, add a few list items, and be sure to include some duplicate values in the Approver field.

Excel example:
Open an Excel file and create two columns: ID and Approver
In the file, create some duplicate email values in the Approver column. Next, we need to create a table from the newly entered data. If you skip this step, the flow will not work. In one of the rows you just created, select a cell, then from the ribbon click Insert, then Table. A little pop-up will appear to confirm what data the table should include. Ensure that “My table has headers” is checked, then click OK to save the change. Again, if you fail to create the table, the flow will not be able to handle the Excel file.



Now, save / upload the Excel file to your OneDrive. You can do this by opening OneDrive from your computer or browsing to it. If you are unsure how to access OneDrive, you can navigate to this URL and select OneDrive from the available options. https://m365.cloud.microsoft/apps?auth=2&home=1

SharePoint list flow example
Navigate to https://make.powerautomate.com/
On the left side of the screen, click My flows, + New flow, then Instant cloud flow.

Yes, you can select automated cloud flow, but for testing, I want to trigger the flow on demand easily.

When the build a flow window opens, enter a flow name, select Manually trigger a flow, and click Create. If you do not see this trigger action, click skip, and you can search for it on the next page that opens.


NOTE: For this demo, I will be using the new flow designer UI. If you are using the classic designer, the same steps will still apply. No, I’m not a fan of the new UI, but I’m guessing people reading this might be forced to use it.

In the flow designer, click the + button below Manually trigger a flow action. In the window that opens to the left, search for SharePoint, and select get items.



In the Get Items action, select your SharePoint site and your list name.
Click the + below the Get Items action, in the window search for select, and select it in the Data Operations group.
With the Select action…selected, click the From field on the right side of the screen, then click the little lightning bolt icon to open the dynamic content menu. If you don’t see the icon, simply put a forward / slash in the From field, then select insert dynamic content. From the dynamic content window, there should be a single item listed below Get items, click on it.


While the Select action is still open, click the T to the right of the Map field values. Do not skip this step!

Open the dynamic content window, search for Approver, and select Approver Email.
Yes, you will see an error, but you can ignore it.


At this point, the flow should look like this. Save and test it (top right).



Next, we are going to use a union function to get a unique list (collection) of email addresses from the Select action.

Below the Select action, click the +, and search for Compose and click it. With the Compose action open, open the expression window. In the same way you opened the dynamic content window, select fx or select insert expression.
In the expression window, enter this: union(body('Select'), body('Select'))
Click the Add button. Your Compose should now look like this:

This is a little hack that combines the Select action with itself, returning a unique set of email addresses.

Save and test the flow. If you click on the Compose action and look at the Outputs, you should see a unique list of emails.

But wait…there’s more! How do you use this in an email action? Add another Compose action below the current one. In the new Compose action, open the expression window and enter the following: join(outputs('Compose'), ';')

Again, save and test. The output of the second Compose can now be used in a send email action. In the send an email action, click the small gear icon on the right side of the To field, select dynamic content, and select the second Compose. You can rename the flow actions to make them less generic.

Complete flow:

Excel flow example
In this section, I’m going to breeze through the steps much more quickly. If you are unsure about something, scroll up and review the SharePoint list flow example section.

Create a new flow that is manually triggered.
Add an action to get the rows from your Excel file. In my example, I’m using the list rows present in a table under the Excel Online (Business) section.


In the Excel action, I’ve selected the following values. Yes, yours will be different. If you are unable to choose a value for Table, it means you skipped the key step at the beginning of this post. Start over…


Below the Excel action, click the + and add a Select action.
From: body/value
Map: click the T –> dynamic content, in the Excel section, click See more, and select Approver

The populated Select should look like this:

If you did not click the list T on the right of the Map field, this will not work.

Below the Select action, click the +, and search for Compose and click it. With the Compose action open, open the expression window. In the same way you opened the dynamic content window, select fx or select insert expression.
In the expression window, enter this: union(body('Select'), body('Select'))
Click the Add button. Your Compose should now look like this:

How do you use this in an email action? Add another Compose action below the current one. In the new Compose action, open the expression window and enter the following: join(outputs('Compose'), ';')

Again, save and test. The output of the second Compose can now be used in a send email action. In the send an email action, click the small gear icon on the right side of the To field, select dynamic content, and select the second Compose. You can rename the flow actions to make them less generic.


Once you get the data from the Excel file, the steps are the same as the first example in this post. In another post like this, I step through this Excel action in more detail: Power Automate: Sending Emails Using Excel Data

For the most part, the above examples apply to many data sources beyond SharePoint or an Excel file stored in OneDrive or SharePoint. Using the Union and Join functions are the core parts of the flow to get a unique list of users or other duplicate values.