Remove duplicate records in collection

(0) Share

Report

Posted on by TechieJones

Hello,

I am having difficulty removing duplicate records from a collection.

Consider these collections:

Collection1 named "colDisplayNames"
==Column named "displayName"

Collection2 named "colUsers_WW_with_Office"
==Column named "displayName"
==Column named "Office"
==Column named "Country"
==Column named "Region"

My goal is to update the collection so it only contains a single instance of each record.
So reduce all records from colUsers_WW_with_Office that are exact duplicates of each other down to a single instance of the record.
If every value in every column of the record is the same as some other record, remove the duplicate records, leaving a single instance of the record.

I tried to use the Distinct function, but quickly learned that it only returns a single-column collection.

So in order to get around this, my brain went like this...

I created the colDisplayNames collection, to which I am able to use Distinct to get it down to a unique list of display names.
ClearCollect(colDisplayNames, Distinct(Sort(colUsers_WW_with_Office, displayName), displayName));

I figured I could then loop through the colDisplayNames collection of unique display names and pull a single instance of each record in the colUsers_WW_with_Office collection that matches the display name, which would give me what I want.
But I am not able to get the code to work.
ClearCollect(colUsers_Gallery, Filter(colUsers_WW_with_Office, displayName in colDisplayNames.displayName)) (This code executes without error, but it does not return unique values)
ClearCollect(colUsers_Gallery, First(Filter(colUsers_WW_with_Office, displayName in colDisplayNames.displayName))) (This code executes without error, but it returns the same record)

Thoughts on how I may be able to achieve my goal?

Appreciate the help.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Joel CustomerEffective 3,224 on at

Like (0)

Report

there is a cool power automate trick I saw @ThatAPIGuy do where you union a list with itself and it only returns the unique values. You could do that and return the Distinct list to the app https://www.google.com/amp/s/thatapiguy.tech/2020/04/04/get-distinct-records-using-the-union-expression-in-power-automate/amp/

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft on at

Like (0)

Report
Hi @TechieJones :
Do you want to delete the exact same record in the table colUsers_WW_with_Office?
As you said, directly using Distinct can only get a table with only one column. So your needs need to use special methods to achieve.
I assume these four fields are all text type(displayName/Office/Country/Region).
I've made a test for your reference:
My idea is to merge all the fields together, and then deduplicate the merged fields, and then split the deduplicated table to get a table with no duplicate records. Use this table to update the original collection.

ClearCollect( colUsers_WW_with_Office, ForAll( Distinct( Split( Concat( colUsers_WW_with_Office, displayName & "##" & Office & "##" & Country & "##" & Region, "$$$" ), "$$$" ), Result ), { displayName: Last( FirstN( Split( Result, "##" ), 1 ) ).Result, Office: Last( FirstN( Split( Result, "##" ), 2 ) ).Result, Country: Last( FirstN( Split( Result, "##" ), 3 ) ).Result, Region: Last( FirstN( Split( Result, "##" ), 4 ) ).Result } ) )

Best Regards,
Bof

Was this reply helpful? Yes No
TechieJones 17 on at

Like (0)

Report

Thank you @v-bofeng-msft. I will test and report back.

Thank you @jlindstrom... cool use case.

Was this reply helpful? Yes No
TechieJones 17 on at

Like (1)

Report

Man, @v-bofeng-msft, your solution really is clever.
It works perfectly.
I am going to save that as my first method of de-duplicating a multi-field collection. 😉
I am working with only a thousand records or so, but the processing of your code is fast, nonetheless.
Thank you soo much for the help.

As for @jlindstrom I am going to save your idea as a tool, as well, because it has use cases beyond Power Apps.
Thank you.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi v-bofeng-msft

What if I just have one column out of five I want to remove duplicates from?

Br. Tore

Was this reply helpful? Yes No
james_hathaway 427 on at

Like (0)

Report
I found a pretty good way of removing duplicates from a collection that uses the GroupBy and UnGroup functions:

col_Original = Original Collection that contains Duplicates
col_Distinct = Collection with the same columns as the original, but without Duplicate values in the "ColumnToDistinct" Column.

ClearCollect(col_Distinct, UnGroup( DropColumns( AddColumns( GroupBy(col_Original,"ColumnToDistinctOn","Rows"), "FirstRow", Table(First(Rows)) ), "Rows" ), "FirstRow" ) )

Adding the Column "FirstRow" as a TABLE and Not a Record is key to making the Ungroup work properly.

Dropping the Column "Rows" is not strictly necessary, and might actually be useful if you need it, but I included it to make sure the end Collection has the same columns as the original.

If you don't have a "ColumnToDistinctOn", just use AddColumns to create a calculated "tuple" column to distinct (and DropColumns to remove again if you like)

Hope this helps!

Was this reply helpful? Yes No
skydivertak 64 on at

Like (1)

Report

I found this elegant solution using ThisRecord property...
Remove Duplicate Rows From A Power Apps Collection - PowerApps Development Auckland New Zealand

Was this reply helpful? Yes No