Forum Discussion
Solved
Power Query - max date by product
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: ...
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
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