Get Unique / Distinct Records & Their Count
Gives the largest / most recent or the smallest / oldest records for unique values in given columns. Kind of a Group By, then Max or Min.
The template uses a SharePoint list to demonstrate, but it will work for SharePoint, Excel, SQL, Dataverse, API calls to external datasets, and any other dataset used in Power Automate.
This method ultimately pulls in the data as a standard JSON array and processes it to identify all the duplicate records based on the selected columns. From there it selects only records with unique (non-duplicate) values for the selected columns.
So you can get the entire record for unique values in any given column(s).
Example...
Use any get data action to pull the data into the flow. Use built in action functions like Order By or later use a Power Automate Sort( ) expression and Power Automate Reverse expression to format your data in ascending or descending order by your chosen column.
Sorting by a date field, Ascending will get the the oldest unique records, Descending will get the newest unique records.
Sorting by a number field, Ascending will get the the smallest unique records, Descending will get the largest unique records.
Move to the Select DuplicateCheckFields action and fill the column name & dynamic content value for each data source column you want to include in the check for duplicates. Only records where all listed columns match will count as duplicates. So any records where any of the listed columns do not match other records will count as unique records.
The middle actions will process the records, identify the duplicate records, & return a JSON array of unique / distinct records based on the previously selected columns.
Demonstration SharePoint List with Duplicates
Demonstration unique SharePoint records JSON output
Demonstration unique SharePoint records JSON output in an HTML table
Notice rows with IDs 29 & 34 are included. Although their Title values match Title values in other rows, their Column2 values are unique among all the other rows. Since the demonstration specified to check both Title & Column2 fields for unique values, it still returned these rows with unique Column2 values.
Thanks for any feedback, & please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86)
V1.1 added a Reverse( ) expression in the processing actions to make the sorting & records returned more intuitive for the user-inputs.
V1.2 adds a UniqueItemCount property to the final records that gives the count of records that shared the same values for the set columns. So now this template can also act as something that returns a type of groupby max or min value alongside a count of each group.
If you have any trouble importing the flow from the standard legacy method, you can also try to import the flow as a Power Apps Solution package: Re: Get Distinct Records & Counts - Power Platform Community (microsoft.com)
Version 1.2
V1.2 adds a UniqueItemCount property to the final records that gives the count of records that shared the same values for the set columns. So now this template can also act as something that returns a type of groupby max or min value alongside a count of each group.
The new expression for this in the Map input of the Select ResetToRecordWithIsDuplicateField is...
addProperty(
addProperty(
item()?['RecordJSON'],
'IsDuplicate',
if(greater(length(split(join(skip(split(string(body('Select_ReformatRecordAndDuplicateChecks')), string(item())), 1), ''), string(item()?['DuplicateFieldsJSON']))), 1), 1, 0)),
'UniqueItemCount',
sub(length(split(string(body('Select_ReformatRecordAndDuplicateChecks')), string(item()?['DuplicateFieldsJSON']))), 1)
)
Alternate Solution Import
This Power Apps Solution import holds both the Find & Remove Duplicates flow and the Get Distinct Records flow. You can import the solution & then find them in the Flows section of the solution.
