Select the latest data based on the date column

[Anonymous]

Is there a way to filter the data based on the date column. For example -

Product	Cost	Date
1	3	1/4/2023
1	4	12/5/2022
1	5	12/20/2022

From the above table, I only need 1st row for product 1 which has the latest date and it would be the same for other products.

Product	Cost	Date
1	3	1/4/2023

[Anonymous]

That's what I said! 🙂

[AlexisOlson]

There are lots of ways to do this. I've written up a few of them here:
https://community.powerbi.com/t5/Community-Blog/Select-Distinct-Rows-Ordered-by-Another-Column-Power...

[Anonymous]

Even easier to group by and add the max aggregation to the date column in the same GUI dialog.

--Nate

[BA_Pete]

Except that it doesn't guarantee that you get the correct [cost] value associated with the latest [Date] row.

Pete

[ouaelaam]

Hi,

This post explains how to do it for Decimal value, you can follow the same approach for Dates :
https://gorilla.bi/power-query/using-table-max/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete · Accepted solution]

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