Power Query - max date by product

Hi mb0307 

To add a column like this, I would typically do a self-join (on the Product column in this case) and aggregate to get the max date.

 

Sample M code (last 2 steps are the important ones):

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUt9Q3MjAyVIrVwSFgjC5ggixgBNJiqA9EBIWM8QrFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Date", type date}}),
    #"Merge with Self" = Table.NestedJoin(#"Changed Type", {"Product"}, #"Changed Type", {"Product"}, "JoinOnProduct", JoinKind.LeftOuter),
    #"Aggregate Max Date" = Table.AggregateTableColumn(#"Merge with Self", "JoinOnProduct", {{"Date", List.Max, "Max Date per Product", type date}})
in
    #"Aggregate Max Date"

 

Regards

Owen