Forum Discussion
Max Row for Another Column - M Query
I have a table of about 40 columns which I would like to filter down to only those rows with the maximum version: OS_ID OS_Version 1 1 1 2 2 1 2 2 2 3 3 1 The r...
Anonymous - Here is an improved version that keeps all of the information in the maximum OS_Version row and only that row:
Updated PBIX is attached
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY2xDoMwDER/pcrMAvxCl04MSCxR1KE6GkshlpJA+/nYKMBwtvzubFtrhvH9eppG+4SUiaMMIy94fDisyzVx8Ugnc401rTgqjpCaseHmnaj8WCro68vBu5ovPkE3IsniaahmXpP69Y6iXjFtGke4PvT1Uqb/8QdBEs7t", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"OS_ID", Int64.Type}, {"OS_Version", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"OS_ID"}, {{"OS_Version", each List.Max([OS_Version]), type number}, {"OtherRows", each _, type table [OS_ID=number, OS_Version=number, Some column=text, Some other column=text]}}), #"Expanded OtherRows" = Table.ExpandTableColumn(#"Grouped Rows", "OtherRows", {"OS_Version", "Some column", "Some other column"}, {"OtherRows.OS_Version", "OtherRows.Some column", "OtherRows.Some other column"}), #"Added Custom" = Table.AddColumn(#"Expanded OtherRows", "Keep", each if [OS_Version] = [OtherRows.OS_Version] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = 1)) in #"Filtered Rows"
Can you use Group by like this?
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8g+O93RR0gHRYalFxZn5eUqxOtFKhkAhQzjLCMwygosZoYgZg1nGENlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"OS_ID", Int64.Type}, {"OS_Version", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"OS_ID"}, {{"OS_Version", each List.Max([OS_Version]), type number}})
in
#"Grouped Rows"
Except this now removes all of the other columns associated with the max version. How do I get them back?
Anonymous - Here is an improved version that keeps all of the information in the maximum OS_Version row and only that row:
Updated PBIX is attached
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY2xDoMwDER/pcrMAvxCl04MSCxR1KE6GkshlpJA+/nYKMBwtvzubFtrhvH9eppG+4SUiaMMIy94fDisyzVx8Ugnc401rTgqjpCaseHmnaj8WCro68vBu5ovPkE3IsniaahmXpP69Y6iXjFtGke4PvT1Uqb/8QdBEs7t", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"OS_ID", Int64.Type}, {"OS_Version", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"OS_ID"}, {{"OS_Version", each List.Max([OS_Version]), type number}, {"OtherRows", each _, type table [OS_ID=number, OS_Version=number, Some column=text, Some other column=text]}}), #"Expanded OtherRows" = Table.ExpandTableColumn(#"Grouped Rows", "OtherRows", {"OS_Version", "Some column", "Some other column"}, {"OtherRows.OS_Version", "OtherRows.Some column", "OtherRows.Some other column"}), #"Added Custom" = Table.AddColumn(#"Expanded OtherRows", "Keep", each if [OS_Version] = [OtherRows.OS_Version] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = 1)) in #"Filtered Rows"You have to add an aggregation using the Advanced option to keep All Rows. Then you expand after the Group By. I have attached an example PBIX.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY2xDoMwDER/pcrMAvxCl04MSCxR1KE6GkshlpJA+/nYKMBwtvzubFtrhvH9eppG+4SUiaMMIy94fDisyzVx8Ugnc401rTgqjpCaseHmnaj8WCro68vBu5ovPkE3IsniaahmXpP69Y6iXjFtGke4PvT1Uqb/8QdBEs7t", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"OS_ID", Int64.Type}, {"OS_Version", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type1", {"OS_ID"}, {{"OS_Version", each List.Max([OS_Version]), type number}, {"OtherRows", each _, type table [OS_ID=number, OS_Version=number, Some column=text, Some other column=text]}}), #"Expanded OtherRows" = Table.ExpandTableColumn(#"Grouped Rows", "OtherRows", {"Some column", "Some other column"}, {"OtherRows.Some column", "OtherRows.Some other column"}) in #"Expanded OtherRows"This is where having representative sample data and expected result are very handy. ImkeF probably has some thoughts on this as well.