MAXIF - remove rows

[francofava]

Hello, I have a table with the following informations, the column unique_id is just the merge of scenario & year:

Period	Amount	scenario	year	unique_id
202001	100	FCT1	2020	FCT12020
202101	150	FCT1	2021	FCT12021
202002	300	FCT1	2020	FCT12020
202103	400	FCT1	2021	FCT12021
202001	250	FCT2	2020	FCT22020
202101	120	FCT2	2021	FCT22021
202002	485	FCT2	2020	FCT22020
202103	695	FCT2	2021	FCT22021

I would like to identify the rows for each unique_id where the biggest period.

Period	Amount	scenario	year	unique_id	Check
202001	100	FCT1	2020	FCT12020	FALSE
202101	150	FCT1	2021	FCT12021	FALSE
202002	300	FCT1	2020	FCT12020	TRUE
202103	400	FCT1	2021	FCT12021	TRUE
202001	250	FCT2	2020	FCT22020	FALSE
202101	120	FCT2	2021	FCT22021	FALSE
202002	485	FCT2	2020	FCT22020	TRUE
202103	695	FCT2	2021	FCT22021	TRUE

To end up with the following table

Period	Amount	scenario	year	unique_id
202002	300	FCT1	2020	FCT12020
202103	400	FCT1	2021	FCT12021
202002	485	FCT2	2020	FCT22020
202103	695	FCT2	2021	FCT22021

Thanks!

[ronrsnfld · Accepted solution]

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

[francofava]

THanks! 

[Anonymous]

try this