Forum Discussion
Anonymous
6 years agoNot applicable
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...
- 6 years ago
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"
Anonymous
6 years agoNot applicable
Except this now removes all of the other columns associated with the max version. How do I get them back?
Greg_Deckler
6 years agoCommunity Champion
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"