Max Row for Another Column - M Query

[Anonymous]

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]

[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

[amitchandak]

The Dax way. Create a new table like

new table=
summarize(table,table[OS_ID],"OS Version",max(Table[OS_Version]))

[Greg_Deckler]

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"

[Anonymous]

@Greg_Deckler 

Except this now removes all of the other columns associated with the max version. How do I get them back?

[Greg_Deckler · Accepted solution]

@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"

[Greg_Deckler]

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.