Forum Discussion

EWBWEBB's avatar
EWBWEBB
Helper III
2 years ago

Remove duplicates based on condition

Hello,

 

I'm trying to establish how I can remove the duplicates from set of data but chose the one to remove based on a condition. 

 

I have a record of people currently off work.

These entries are repeated each month.

I've got power query to sort it all out and return a neat list with a unique identifier of the persons name and the first day they are off. 

 

I want to remove any duplicates so I have a single list, but when one of the duplicates has a return date entered I want to keep that and remove those where the date isn't entered.

 

If there is a duplicate and none have a return date entered then it doesn't matter which one is removed.

 

If there is a duplicate where they both have the same start and return date then it doesn't matter which is removed.

 

Attached is a file that has some sample data and then a 2 additional column I've populated in excel to show if it is a duplicate and then what I would want the decision to be.

 

Hope this makes sense.

 

The below should give you an anonymised sample data

https://we.tl/t-uAGS6a9jQH 

2 Replies

  • Hi EWBWEBB ,

     

    I may be oversimplifying this, but can't you just group on [Name] and [FIRST DAY], then add an aggregated column that is MAX of [RESUMED].

     

    It gives you this:

     

    Pete

    • EWBWEBB's avatar
      EWBWEBB
      Helper III

      I'll give this a go - I thought i tried it and it came up with a strange error but I'll need to loop back to it to try it out. 

      Thanks