M code to retrieve latest Date for repeated ID's

[milomilo2020]

Hi, I have a table with multiples ID's that can be in more than a ROW. They have different dates on each row

I need to replicate the following in a custom column in M , power query:

Logic:

There are repeated ID's with different dates. I need to retrieve the latest date. If the Date is the latest, then I need to have TODAY()

For example, ID = 1200

It appears in two rows. In the first one, 3/1/2022, the expected result is 8/1/2022, because there is another row with ID 1200 and latest date. On the other hand, the row with ID = 1200 and Date = 8/1/2022 has TODAY(), because there is no other newest date for that ID.

Also the dataset has another column which is a row ID.

DATASET for test: https://drive.google.com/file/d/16K8CjMfNRv1CXpMcZZQ5HKtA_dw6G4c4/view?usp=sharing

Thanks!

[v-jingzhang · Accepted solution]

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.