How to get the latest record and remove older one

[powerbidev123]

Hi all

In the power query I have 2 columns 

Date and Id.

Sample dataset is below:

Date.                          Id

01/01/2025.               101

01/09/2025.                101

• The id column contains duplicate and i want to basically remove the record that has the older date i.e 01/01/2025. How can i achieve that in power query?

[samratpbi · Accepted solution]

Hi, If you have 2 columns only and then better to group by ID column and take the Max of the date.

Below is the dataset I took:

then done a Group By

Final output

Hope this helps to resolve your problem. If it does, then please mark it as solution.

Thanks - Samrat

[powerbidev123]

Hi @lbendlin , @samratpbi  thanks for pitching in . I created a new column that using list.max that gives max of the date else the older date will be blank 

[lbendlin · Accepted solution]

The safest way to do that is to use the grouping feature

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMtU31jcyMDJVitUBCRkChQwM9YEISRCkzkTf2ACbQkuoYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Date", each List.Max([Date]), type nullable date}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[samratpbi · Accepted solution]

Hi, If you have 2 columns only and then better to group by ID column and take the Max of the date.

Below is the dataset I took:

then done a Group By

Final output

Hope this helps to resolve your problem. If it does, then please mark it as solution.

Thanks - Samrat