Forum Discussion
Selecting top 2 people from each ID - DAX or PowerQuery
I have a table with IDs and names and I only want to pick top 2 names (alphabetically).. is there a way to achieve this via DAX or in Power Query? I can't do TOP N function since I need to merge several different tables later on so need a logic applied for each table before I merge them by ID.
Any help/advice would be appreciated!
Group by the ID column and choose All Rows as the Operation:
This is the result:
Tweak the generated code slightly to only get the top 2 names. Change "each _" to:
each Table.MinN(_, "Name", 2)Expand the Name column (or all of the non-ID columns) and you're done!
Full sample code you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMrVSK1YGwPRIzc1IR3ODEXDjbMbM4A8wxAnKcEOpAXOfMvBQEzyc1Fcw2BrJDEivhbI/EoiIEzzszF852zEmtQOLkZAINiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Subtable", each Table.MinN(_, "Name", 2), type table [ID=nullable number, Name=nullable text]}}), #"Expanded Subtable" = Table.ExpandTableColumn(#"Grouped Rows", "Subtable", {"Name"}, {"Name"}) in #"Expanded Subtable"
1 Reply
Group by the ID column and choose All Rows as the Operation:
This is the result:
Tweak the generated code slightly to only get the top 2 names. Change "each _" to:
each Table.MinN(_, "Name", 2)Expand the Name column (or all of the non-ID columns) and you're done!
Full sample code you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMrVSK1YGwPRIzc1IR3ODEXDjbMbM4A8wxAnKcEOpAXOfMvBQEzyc1Fcw2BrJDEivhbI/EoiIEzzszF852zEmtQOLkZAINiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Subtable", each Table.MinN(_, "Name", 2), type table [ID=nullable number, Name=nullable text]}}), #"Expanded Subtable" = Table.ExpandTableColumn(#"Grouped Rows", "Subtable", {"Name"}, {"Name"}) in #"Expanded Subtable"