Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Duplicate matches collection keep latest

(0) ShareShare
ReportReport
Posted on by 178

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

 

 

Example.jpg

  • Verified answer
    EddieE Profile Picture
    EddieE 4,641 on at
    Re: Duplicate matches collection keep latest

    @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)

  • christian12 Profile Picture
    christian12 178 on at
    Re: Duplicate matches collection keep latest

    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.

  • EddieE Profile Picture
    EddieE 4,641 on at
    Re: Duplicate matches collection keep latest

    @christian12 

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

     

    Cheers

  • christian12 Profile Picture
    christian12 178 on at
    Re: Duplicate matches collection keep latest

    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

  • EddieE Profile Picture
    EddieE 4,641 on at
    Re: Duplicate matches collection keep latest

    @christian12 

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

  • christian12 Profile Picture
    christian12 178 on at
    Re: Duplicate matches collection keep latest

    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 

  • EddieE Profile Picture
    EddieE 4,641 on at
    Re: Duplicate matches collection keep latest

    @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
     )
    )
    

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,434

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,722

Leaderboard