Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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:
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:
That works, thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |