Forum Discussion
Group by and remove rows based on condition (countrow and max value)
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.
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:
2 Replies
- v-diye-msft
Community Support
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: