MAXIF - remove rows

Paste the code below into a blank query.

Read the comments and explore the Applied Steps to understand the algorithm.

• Group by Unique ID
• Filter each subtable to select the Maximum amount
• Re-expand the grouped subtables and set the columns back to original order

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwVNJRMjQwAJJuziEgDkgUygMzY3XAKg0hKk1RVRoiVBrCVBoYGAFFjYky0xgoamJAjJlgSZjtRihmGmFxpxGqSkOESjR3mliYEmEmyJ1mlqZ4zIwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Amount = _t, scenario = _t, year = _t, unique_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"Amount", Int64.Type}, {"scenario", type text}, {"year", Int64.Type}, {"unique_id", type text}}),

//group by unique id
//  then Select the row with the maximum Amount
    #"Grouped Rows" = Table.Group(#"Changed Type", {"unique_id"}, {
        {"Amount", (t)=>Table.SelectRows(t, each [Amount]=List.Max(t[Amount])),
            type table[Period=Int64.Type, Amount=number, scenario=text,year=Int64.Type]}
        
        }),

//Expand the subtables
    #"Expanded Amount" = Table.ExpandTableColumn(#"Grouped Rows", "Amount", 
        {"Period", "Amount", "scenario", "year"}, 
        {"Period", "Amount", "scenario", "year"}),

//Return columns to original order
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Amount",Table.ColumnNames(Source))
in
    #"Reordered Columns"

Data

Results