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
