Sort names alphabetically but keeping groups with same ID together

Question

I have a list of members of families attending an event that I want to sort alphabetically but some of the people have different surnames within the same family. I still want them to appear together in the list so grouped by ID which is the same for each family but placed alphabetically based on the name of Adult 1

Data starts as table 1

ID	Name	Title
141	Smith	Adult 2
141	Ward	Child 3
141	Ward	Child 1
141	Ward	Child 2
141	Ward	Adult 1
140	Rose	Adult 1
140	Brooks-Rose	Adult 2
138	Bud	Adult 1
136	Filer	Adult 2
136	Filer	Adult 1
120	Heitlinger	Adult 1
120	Heitlinger	Child 2
120	Heitlinger	Child 1
120	Karp	Adult 2
78	Curwen	Adult 1
78	Curwen	Adult 2
71	Polin	Adult 1
71	Polin	Adult 2
66	Cordell	Adult 2
66	Cordell	Adult 1
335	Flax	Adult 1
335	Jones	Child 1
109	Gold	Child 3
109	Gold	Child 1
109	Gold	Child 2
109	Taylor-Gold	Adult 2
109	Gold	Adult 1
171	Hill	Child 1
171	Hill	Adult 2
171	Hill	Child 2
171	Hill	Adult 1

Expected Output

ID	Name	Title
138	Bud	Adult 1
66	Cordell	Adult 2
66	Cordell	Adult 1
78	Curwen	Adult 1
78	Curwen	Adult 2
136	Filer	Adult 2
136	Filer	Adult 1
335	Flax	Adult 1
335	Jones	Child 1
109	Gold	Adult 1
109	Taylor-Gold	Adult 2
109	Gold	Child 1
109	Gold	Child 2
109	Gold	Child 3
120	Heitlinger	Adult 1
120	Karp	Adult 2
120	Heitlinger	Child 1
120	Heitlinger	Child 2
171	Hill	Child 1
171	Hill	Adult 2
171	Hill	Child 2
171	Hill	Adult 1
71	Polin	Adult 1
71	Polin	Adult 2
140	Rose	Adult 1
140	Brooks-Rose	Adult 2
141	Ward	Adult 1
141	Smith	Adult 2
141	Ward	Child 1
141	Ward	Child 2
141	Ward	Child 3

jimmy801 · Accepted Answer

Hello joooffice&nbsp;
&nbsp;
check out this solution. Used Table.Group to group by ID with all rows and the first name found. Then sort for this name and expand afterwards
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLNCsIwEIRfpeSsYBt/j1pQ0Yuo4KH0UEjQ4NpI2qK+vYm22rgJeArst7OT2SRJSNgPSYfsLqI86XPKKiiDKCBpp0GHTDF9xCcBLKBeEnqJa9rb56Pp6dJWFtxNZkrKc9G1GpqhdGwaKjyTDnVpLoArrMGoUUXGb8lFCSI//sXtjD5u6deZuv5eamRyxJW68fzX1IUalVnpRmozJMKk1gxN+lgqxgH+YvVESgdmbZDd3WQlc16gwL2JLi0k4C+EiV8Ttck+e4BU3brBftq21H6510KW4pXLtmsTexrWuMjXJ30C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Title = _t]),
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Title", type text}})),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AllRows", each _, type table [ID=number, Name=text, Title=text]},{"NameToSort", each _[Name]{0}}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"NameToSort", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"AllRows"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Other Columns", "AllRows", {"ID", "Name", "Title"}, {"ID", "Name", "Title"})
in
    #"Expanded AllRows"
&nbsp;
Copy paste this code to the advanced editor in a new blank query to see how the solution works.If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)Kudoes are nice tooHave funJimmy

Forum Discussion

Sort names alphabetically but keeping groups with same ID together

1 Reply

Related content

OneLake storage keeps growing?

Datasets keep losing gateways

Using Import Models and Dataflows Together

Keep First 80%

Deliminate columns but keep like values together