cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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_IDOS_Version
11
12
21
22
23
31

 

The result should be:

 

OS_IDOS_Version
12
23
31

 

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]

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Max Row for Another Column - M Query

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

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Max Row for Another Column - M Query

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"

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Max Row for Another Column - M Query

The Dax way. Create a new table like


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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Max Row for Another Column - M Query

@Greg_Deckler 

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

 

Highlighted
Super User IV
Super User IV

Re: Max Row for Another Column - M Query

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.

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

Re: Max Row for Another Column - M Query

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

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Solution Specialist
Solution Specialist

Re: Max Row for Another Column - M Query

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

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors