Sort names alphabetically but keeping groups with same ID together

[joooffice]

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 solution]

Hello @joooffice 

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"

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 too

Have fun

Jimmy