Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
Solved! Go to Solution.
@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"
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
The Dax way. Create a new table like
new table=
summarize(table,table[OS_ID],"OS Version",max(Table[OS_Version]))
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?
@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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |