Microsoft Power Automate - How to retrieve unique values from a data table faster and more efficiently

How to retrieve unique values from a data table faster and more efficiently

💡 Are you using a For Each action to retrieve unique values from a data table? What if I tell you that there is a faster and more efficient method? Read on to learn more about this hack.

Problem Description

Retrieving unique values from a data table using the "For Each" action may require multiple actions and lead to slower processing within a loop.

Assume you have a data table called '%DataTable%' with columns for names and email addresses.

Solution

The following flow provides a high level overview of the solution:

We will use the “Run .NET script” to perform the checks and returns a data table. Configure the following:

Language: C#
.NET script imports:
Script parameters:

Click “Edit”
Click ‘+’ icon at the bottom left to add parameters
Add the following two parameters:
Add the following code under ‘.NET code to run:’
This code creates a new datatable containing only unique rows based on the "Email" column from an existing datatable. Here's a breakdown of its function:
- Cloning the datatable: uniqueValuesTable is created with the same structure as the original datatable.
- Looping through rows: It iterates through each row in the original datatable.
- Checking for uniqueness: For each row, it checks if the "Email" value already exists in uniqueValuesTable.
- Adding unique rows: If the "Email" value is not found in the new table, the row is added to uniqueValuesTable.
- Result: After processing, dataUniqueEmail contains rows with unique "Email" values.