Overview
SharePoint lists often contain duplicate values across rows—multiple entries with the same email address, department, or status. Power Automate’s union expression combined with the Select action allows you to extract unique values from any column, enabling you to send consolidated reports, identify distinct users, or build dynamic recipient lists without duplicates.
Prerequisites
- Power Automate cloud flow experience
- Understanding of SharePoint lists and columns
- Familiarity with Get items action
- Basic knowledge of expressions and dynamic content
Why Extract Unique Values from Lists?
SharePoint lists frequently contain duplicate values. An expense tracking list might have dozens of entries from the same email address. A project task list could show the same department name across multiple rows. When you need to send a summary email to each unique person, or report on distinct categories, you need a way to filter out duplicates and work with only the unique values.
Power Automate doesn't have a built-in "Get unique values" action, but you can achieve this using two techniques:
- Select action – Isolates a specific column from your list data, creating an array of just that column's values
- union() expression – Compares two arrays and returns only unique values, effectively removing duplicates when you union an array with itself
This guide demonstrates extracting unique email addresses from a SharePoint list, then using those addresses to retrieve user-specific records and send personalised emails. The same pattern works for any column—Status, Department, Category, assigned user, or any other field where you need distinct values.
Example List Structure
For this guide, we'll use an expense submission list where multiple employees submit expenses throughout the month. Each employee might have several expense entries, but we want to send one consolidated email per person showing all their submissions.
List columns:
| Column Name | Type | Purpose |
|---|---|---|
| Title | Single line of text | Expense description |
| Amount | Number | Expense amount in pounds |
| Category | Choice | Travel, Meals, Equipment, etc. |
| Single line of text | Employee email address | |
| Status | Choice | Submitted, Approved, Rejected |
| SubmissionDate | Date and Time | When expense was submitted |
The list will have multiple rows with the same email address—John might have submitted five expenses, Sarah might have submitted three. Our goal: extract the unique email addresses (John, Sarah, etc.), then for each unique person, get all their expense records and email them a summary.
The first step will be to run a scheduled flow that retrieves all list items and extracts the unique email addresses. Below is an example SharePoint list we'll use.
The flow will run on a schedule (e.g., first Monday of each month) – Author
Creating the Unique Values Flow
The flow logic consists of several steps: retrieve all list items, select just the email column, remove duplicates, then loop through each unique email to get that person's records.
Set frequency to monthly, running on the first Monday (or your preferred schedule).
From SharePoint connector, select your site and expense list. Leave Filter Query and Top Count empty to retrieve all items.
This action maps an array to extract specific properties. In From, select "value" from the Get items output. Under Map, click "Enter key" and type any label (e.g., "Email"). In the value field, select the Email column from Get items dynamic content.
The Select action output is now an array containing only email addresses: ["john@example.com", "sarah@example.com", "john@example.com", "mike@example.com", "sarah@example.com"]
Notice the duplicates—John and Sarah appear multiple times because they have multiple expense submissions. Now we'll remove those duplicates.
Using Union to Remove Duplicates
The union() expression takes two arrays and returns all unique values that appear in either array. When you pass the same array twice, it returns that array with duplicates removed.
Use a Compose action to hold the result of the union expression. Click in the Inputs field, then switch to the Expression tab.
Type this expression (you'll need to click between the Expression tab and Dynamic content tab to build it):
union(body('Select'),body('Select'))This tells Power Automate: take the output of the Select action, union it with itself, and return only unique values.
After the Compose action runs, the output is now: ["john@example.com", "sarah@example.com", "mike@example.com"]
All duplicates removed—we now have one entry per unique email address. This array becomes the basis for our loop in the next step.
Looping Through Unique Values
Now that we have an array of unique email addresses, we can loop through each one and retrieve all expense records for that specific person.
For the input, select "Outputs" from the Compose action (the one containing your union expression). This loops through each unique email address.
Add another Get items action. Configure for the same SharePoint site and list. This time, use Filter Query to retrieve only records for the current user.
In the Filter Query field, enter an OData filter that matches the Email column to the current item in the loop. The filter syntax is:
Email eq '@{items('Apply_to_each')?['Email']}'This filters the list to return only items where the Email column equals the current unique email being processed in the loop.
Important: If the internal column name differs from the display name, use the internal name in the filter. If your column is called "Employee Email" but the internal name is "EmployeeEmail", the filter becomes:
EmployeeEmail eq '@{items('Apply_to_each')?['Email']}'Now for each unique email address (John, Sarah, Mike), the flow retrieves all expense records belonging to that person. The Get items output inside the loop contains only records where the Email column matches the current user being processed.
Creating an HTML Table to Display Records
Now that we have all expense records for the current user, we can format them into an HTML table to include in an email. Power Automate's Create HTML table action converts an array of objects into a formatted HTML table automatically.
Search for "Create HTML table" from the Data Operations connector.
Click in the From field and select "value" from the Get items action (the second one inside your loop, not the first one at the start of the flow).
Click "Show advanced options". Change Columns from "Automatic" to "Custom". Add only the columns you want in the email table (Title, Amount, Category, SubmissionDate). This prevents internal SharePoint metadata columns from appearing in your table.
The Create HTML table output is now an HTML <table> element containing all the user's expense records with proper rows and columns. You can insert this directly into an email body.
Configure the To field with the current loop item's email: select "Current item" from Apply to each, then select "Email" property.
Subject example: "Your Expense Summary for [Month]". In the Body, insert the Create HTML table output to display the user's records.
Complete flow logic summary:
- Trigger: Scheduled recurrence
- Get all items from SharePoint list
- Select: Extract Email column to array
- Compose: Apply union to remove duplicates
- Apply to each unique email:
- Get items filtered by current email
- Create HTML table from filtered records
- Send email to current user with their table
Each unique person receives one email containing an HTML table with all their expense submissions. No duplicates, no manual filtering required.
Next Steps
You now understand how to extract unique values from SharePoint lists using the Select action and union expression, then use those values to retrieve and process records for each distinct item. This pattern works for any column type—email addresses, departments, categories, statuses, or custom choice fields.
Extend this technique by exploring:
- Multiple unique columns – Extract unique combinations of two columns (e.g., Department + Status) by selecting both in the Map and adjusting the Filter Query accordingly
- Aggregating data – Calculate totals per unique user using Compose actions with sum(), length(), or other aggregate expressions before building the HTML table
- Conditional unique extraction – Filter the initial Get items by Status or Date before extracting unique values, allowing you to report on distinct active users, pending items, etc.
- Dynamic recipients from unique values – Build a semicolon-separated string from unique email addresses to send one summary email to all distinct users:
join(body('Compose'),';') - Sorting HTML tables – Add Order By to the filtered Get items action to control how records appear in each user's table
- Using distinct() expression (preview) – Power Automate is introducing a distinct() expression in some tenants as an alternative to union for deduplication
The Microsoft workflow expression reference for union provides additional examples and syntax details for array manipulation and deduplication techniques.