Remove duplicates based on condition

[EWBWEBB]

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 

[BA_Pete]

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]

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