Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
Proud to be a Datanaut!
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
41 | |
30 | |
26 | |
21 | |
17 |