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 result should be:
| OS_ID | OS_Version |
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
This should then include all the columns that are associated with the above maximum version number based on the OS_ID.
So far I have:
let
Source = Sql.Databases("database"),
#"database" = Source{[Name="database"]}[Data],
dbo_OS_Hist = #"database"{[Schema="dbo",Item="OS_Hist"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_OS_Hist, each Date.IsInPreviousNMonths([UPDATED_DATE], 300)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([OS_ID] = List.Max([OS_VERSION])))
in
#"Filtered Rows1"
This is the row where I am having trouble:
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([OS_ID] = List.Max([OS_VERSION])))
This is the error Im getting:
Expression.Error: We cannot convert Type to List type.
Details:
Value=[Type]
Type=[Type]
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"
6 Replies
- Greg_Deckler
Community Champion
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?
- Greg_Deckler
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"
- amitchandak
Super User
The Dax way. Create a new table like
new table=
summarize(table,table[OS_ID],"OS Version",max(Table[OS_Version])) - FrankAT
Hi,
with your sample data follow these steps in Power Query Editor:
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLCMwygosZoYgZg1nGENlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [OS_ID = _t, OS_Version = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OS_ID", Int64.Type}, {"OS_Version", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"OS_ID"}, {{"Group", each List.Max([OS_Version]), type number}}) in #"Grouped Rows"Regards FrankAT
