Power BI: Create a relative column in power query based on the highest value

lets say I have the following table:

Year	Patch	Value
2021	1.65	23.5
2021	1.67	21.3
2021	1.70	25.5
2022	1.72	23.2
2022	1.75	21.5
2022	1.79	24.5
2023	1.81	23
2023	1.84	25.5
2023	1.89	28.5

I want to create a column with a boolean value "new" and "old" in power query based on the year and the patch version. There are many patch versions in a year. The highest one from the year is always "new". But the patch before should be "old".

Like getting a result like this:

Year	Patch	Value	Patch Type
2021	1.67	21.3	old
2021	1.70	25.5	new
2022	1.75	21.5	old
2022	1.79	24.5	new
2023	1.84	25.5	old
2023	1.89	28.5	new

Is there a way in power query to get the nearest value based on the latest patch?

Thanks!

Solution

Here you go.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Patch", type number}, {"Value", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {{"All", each _, type table [Year=nullable number, Patch=nullable number, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", (x)=> 
    [ 
    a = Table.FirstN( Table.Sort(x[All],{ {"Patch", Order.Descending}} ),2),
    b = Record.AddField(a{0}, "Patch Type", "new"),
    c = Record.AddField(a{1}, "Patch Type", "old"),
    d = Table.FromRecords({b,c})
    ][d]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Year", "All"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Year", "Patch", "Value", "Patch Type"}, {"Year", "Patch", "Value", "Patch Type"})
in
    #"Expanded Custom"