M code to retrieve latest Date for repeated ID's

Hi milomilo2020 

You can follow below steps. 

1. Select ID column, group by ID column and add two new columns. 

Max Date: Max on Date column;

All Data: All Rows 

2. Expand All Data column and select the columns you want to keep. 

3. Add a custom column with 

if [Date] = [Max Date] then Date.From(DateTime.LocalNow()) else [Max Date]

4. Sort by Index (Row ID) column, reorder column positions and remove Max Date column. You will get the expected result. 

Full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZczBDQAhCETRXjibCAO49kLsvw2VvZhwInlkfgQJNRJmPgcMdOlCqwXhOl7XdL1u/rilW+mMdC8d8XyMEgLn4ysl/ItZFnoWawM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, ID = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"ID", Int64.Type}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Max Date", each List.Max([Date]), type nullable date}, {"All Data", each _, type table [Index=nullable number, ID=nullable number, Date=nullable date]}}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"Index", "Date"}, {"Index", "Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All Data", "Custom", each if [Date] = [Max Date] then Date.From(DateTime.LocalNow()) else [Max Date]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Max Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "ID", "Date", "Custom"})
in
    #"Reordered Columns"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.