Forum Discussion
Solved
Removing duplicates
Hello Below is the sample excel table where I want to remove the duplicates Tried to remove duplicates in Power BI, sorting by ID and DOB and used Max function for each field. The sn...
Looks like you have a data quality problem. One person cannot have two mothers, last I checked.
When you have duplicates like this, who is to day one Mother's Firstname is right or wrong? Based on the fact that one row has DOB and one not?
Try this: On DOB column, replace NULL with some real early date, like 1/1/1930. Now DUPLICATE the dataset (don't reference). Aggregate that by ID, and take MAX(DOB). Now JOIN that to the original dataset, and join on ID and DOB, with INNER JOIN. Lastly, replace 1/1/1930 with NULL.
Feel free to mark replies as "Accepted Solution" if appropriate.