Deleting duplicate based on Date Column

[anabianco09]

Hi all, I am trying to delete some duplicate numbers based on a Date Column, i.e., I want to delete the old and keep only the newest record.

How to do this?

Thanks

[v-cgao-msft · Accepted solution]

Hi @anabianco09 ,

I'm guessing you want to group by the [Ebgine Serial #] field and keep the records with the latest date in each group.
If so, you can:
1. [Ebgine Serial #] in ascending order, [Estimated Arrival Date] in descending order.

2. Group rows by [Ebgine Serial #]  and add a index column:

3. expand data and filter rows and remove index column:

Advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyNNAHIiMDI2MQx1ApVgcmYagPRFAJIyOwhJMTug5jYyQJZB0mJkgSRvpABJUwNVWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ebgine Serial #" = _t, #"Estimated Arrival Date" = _t, #" Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ebgine Serial #", type text}, {"Estimated Arrival Date", type date}, {" Value", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Ebgine Serial #", Order.Ascending}, {"Estimated Arrival Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Ebgine Serial #"}, {{"Data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Estimated Arrival Date", " Value", "Index"}, {"Estimated Arrival Date", " Value", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Index] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

If the latest date has multiple records, you can add a custom column:

Then filter rows:

= Table.SelectRows(#"Added Custom", each [Estimated Arrival Date] = [Latest Date])

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[anabianco09]

Thanks Gao! That worked!

[vicky_]

It's a little unclear what you mean by duplicate records. 
If it's about only keeping records from the latest date, you can filter the date and choose the "Is Latest" option.

Otherwise, if you mean that there's a certain key value, then please try and follow the guide here: https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/