Select the latest data based on the date column

Hi Anonymous ,

 

Select your [Product] column and go to the Home tab > Group By.

Call the aggregated column 'data' and use the All Rows operator.

Now add a new custom column with the following code:

 

Table.Max([data], "Date")

 

 

This will give you a nested RECORD column that you can expand to reinstate the columns you need from the row that has the max date per product.

 

Working example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc1BCkAhCIThu7gOdMaC3lmi+1/jKUHh4t98jLiWQJp4ZF0NSqPLbod78lAwmZdHRCvMoJlraBwU/iJMBSrDcu7v5/4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Cost = _t, Date = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Cost", Int64.Type}, {"Date", type date}}),
    groupProduct = Table.Group(chgTypes, {"Product"}, {{"data", each _, type table [Product=nullable number, Cost=nullable number, Date=nullable date]}}),
    addMaxRecord = Table.AddColumn(groupProduct, "maxRecord", each Table.Max([data], "Date")),
    expandMaxRecord = Table.ExpandRecordColumn(addMaxRecord, "maxRecord", {"Cost", "Date"}, {"Cost", "Date"})
in
    expandMaxRecord

 

Example output:

 

Pete