Create a new column returning the maximum date over an other column as reference

Question

I would like to create a new column on my power query code here, to validate and return the maximum date, using another column as a reference,

For example, in my attached image or table below, from the column "Number", I would like to get the most recent date on the second column and return "Yes", for example, on a new column.

Number	Date	NewColumn
1	23/12/2022	No
1	23/12/2022	No
1	30/12/2022	No
1	06/01/2023	No
1	13/01/2023	No
1	13/01/2023	Yes
2	20/01/2023	No
2	27/01/2023	No
2	03/02/2023	No
2	10/02/2023	No
2	10/02/2023	Yes

Example

Answer 1

You can try this in powerquery

Right click and group on the number column, take the max of date, and all rows

Use the arrows atop the new column to expand the Date column.

the add column, custom column with formula (assumes column was named MaxDate, otherwise use your own name)

= if [Date]=[MaxDate] then "Yes" else  "No"

right click and remove extra column

file .. close and load .. back into excel as table or pivot report (pivot table)

full sample code you could paste into home ... advanced editor ...

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Number"}, {{"MaxDate", each List.Max([Date]), type nullable date}, {"data", each _, type table [Number=nullable number, Date=nullable date]}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date"}, {"Date"}),
#"Added Custom" = Table.AddColumn(#"Expanded data", "Custom", each if [Date]=[MaxDate] then "Yes" else  "No"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"MaxDate"})
in  #"Removed Columns"

Alternate To be fancy instead of all of the above, you can just add column .. custom column ... with below. But it will be slower with larger data sets

= (x)=>if x[Date]=List.Max(Table.SelectRows(#"PriorStepNameHere", each [Number]=x[Number]) [Date]) then "Yes" else "No"