Group by and remove rows based on condition (countrow and max value)

[Anonymous]

Greetings,

I had this table:

I wanted to group by Letter and Number but filtering by the 4 most recent entries. With the help of this answer by @MarcelBeug I have already solved this:

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Letter", "Number"}, {{"AllData", each _, type table [Letter=text, Number=number, Value=number, Date=date]}}),
    Last4 = Table.TransformColumns(#"Grouped Rows", {{"AllData", each Table.MaxN(_, each [Date], 4)}}),
    #"Expanded AllData" = Table.ExpandTableColumn(Last4, "AllData", {"Value", "Date"}, {"Value", "Date"}),

 

But now I´m stuck.

Letter	Number	Value	Date
A	1	5	30/08/2019
A	1	1	26/08/2019
A	2	6	31/08/2019
A	2	57	31/08/2019
A	2	8	30/08/2019
A	2	4	29/08/2019

I need to remove the max [Value] for each group if the group has more than 2 rows. I am working with this answer but not getting there yet.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKEYiMzfQMLfSMDQ0ulWB2IlBEMm2OVMgbRFlilTEC0JYYUyB5TkE4DrLrMQFKGWKVMzXHLWaCZGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Letter = _t, Number = _t, Value = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Date", type date}, {"Number", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Letter", "Number"}, {{"AllData", each _, type table [Letter=text, Number=number, Value=number, Date=date]}}),
    Last4 = Table.TransformColumns(#"Grouped Rows", {{"AllData", each Table.MaxN(_, each [Date], 4)}}),
    #"Expanded AllData" = Table.ExpandTableColumn(Last4, "AllData", {"Value", "Date"}, {"Value", "Date"}),
    #"Grouped Rows1" = Table.Group(#"Expanded AllData", {"Letter", "Number"}, {{"AllData2", each _, type table [Letter=text, Number=number, Value=number, Date=date]}, {"N_Rows", each Table.RowCount(_), type number}})
in
    #"Grouped Rows1"

Best regards.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Let me know if you'd like to get below results:

1. Duplicate the table as table2

Add below M code:

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

2.  Merge with table using Left Anti: (Table is left,Table2 is right)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYlMgNjbQN7DQNzIwtFSK1UFIgbCRGYaUERCbgXQZYpUyNcctZ4HdMpCUCYi2REjFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Letter = _t, Number = _t, Value = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Letter", type text}, {"Number", Int64.Type}, {"Value", Int64.Type}, {"Date", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Letter", "Number", "Value"}, Table2, {"Letter", "Number", "Value"}, "Table (4)", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Table (4)"})
in
    #"Removed Columns"

3. Final results:

[Anonymous]

That works, thanks!