Power Query - max date by product

[mb0307]

Hi,

I need help with PowerQuery - thanks in advance.

I have a table below:

I need to create a Custom Column in PowerQuery showing Max Date per Product.  

Result in same table:

Thanks.

[OwenAuger · Accepted solution]

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