Duplicate matches collection keep latest

(0) Share

Report

Posted on by christian12

204

Hi Team,

I have a collection where the Date column is my key identifier for duplicate values. There can be multiple matches for the same date.

So basically what I need is any matches found for the Date column I need to keep the latest row based on the [ID] date & remove the duplicates. This is to be done across the whole data set for all the dates. If there are no duplicates found for a specific date then no need to delete the row.

Any help is greatly appreciated.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

EddieE 4,641 Moderator on at

Like (0)

Report
Copy link

Link copied!
@christian12

Assuming that ID is of type Text and the Max of the last 2 characters of ID describes the record you are looking for

eg in your example, 06 beats 02, then try this

RemoveIf( yourCollectionName, !(ID in ForAll( yourCollectionName As _data, If( CountRows( Filter(yourCollectionName, DATE = _data.DATE)) = 1 || Value( Right( _data.ID,2)) = Value( Right( First(Sort( Filter(yourCollectionName, DATE = _data.DATE), ID, SortOrder.Descending)).ID, 2)), { VALUE: _data.VALUE, DATE: _data.DATE, ID: _data.ID } ) ).ID ) )

Was this reply helpful? Yes No
christian12 204 on at

Like (0)

Report
Copy link

Link copied!

So both are text although should be referred to as dates. What I have done is sorted the collection by Date column then by ID column. So basically should be in the correct order where we should keep the first line which is a match and can delete the duplicates after it.

Any help with this ?

Thanks again @EddieE

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (0)

Report
Copy link

Link copied!

@christian12

Did you try my code? I tested it on a small collection of data and it worked for me.

Was this reply helpful? Yes No
christian12 204 on at

Like (1)

Report
Copy link

Link copied!

All good fixed it 🙂

Just my only concern is taking the last 2 digits. Where it might be 2022-01-01 & 2021-16-12. The code will take the wrong value

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (0)

Report
Copy link

Link copied!

@christian12

So is it working now? If so, please mark a solution for anyone else who may have a similar problem to solve.

Cheers

Was this reply helpful? Yes No
christian12 204 on at

Like (0)

Report
Copy link

Link copied!

Yes although wont work for all scenarios. Example my only concern is taking the last 2 digits. Where it might be 2022-01-01 & 2021-16-12. The code will take the wrong value.

Was this reply helpful? Yes No
Verified answer

EddieE 4,641 Moderator on at

Like (1)

Report
Copy link

Link copied!
@christian12

It works for all case for me. See the code below where there are 3 same dates with the last 2 characters as 01, 06 and 12 and the code only returns the 12 record, ie

Add this code to a button and test

ClearCollect( colMatches, {VALUE: 111, DATE: Date( 2021, 9, 28), ID: "2021-09-28"}, {VALUE: 121, DATE: Date( 2022, 5, 26), ID: "2022-05-26"}, {VALUE: 121, DATE: Date( 2022, 6, 1), ID: "2022-06-01"}, {VALUE: 121, DATE: Date( 2022, 6, 1), ID: "2022-06-06"}, {VALUE: 121, DATE: Date( 2022, 6, 1), ID: "2022-06-12"}, {VALUE: 117, DATE: Date( 2022, 2, 4), ID: "2022-02-04"}, {VALUE: 117, DATE: Date( 2022, 2, 4), ID: "2022-02-10"} ); RemoveIf( colMatches, !(ID in ForAll( colMatches As _data, If( CountRows( Filter(colMatches, DATE = _data.DATE)) = 1 || Value( Right( _data.ID,2)) = Value( Right( First(Sort( Filter(colMatches, DATE = _data.DATE), ID, SortOrder.Descending)).ID, 2)), { VALUE: _data.VALUE, DATE: _data.DATE, ID: _data.ID } ) ).ID ) )

The only time this code won't work is if you get more than 99 records with the same date (ie last 2 characters are 99)

Was this reply helpful? Yes No