Power Query - Need help to group and sort column Member ID and sort column Member Name accordingly

[khom]

Hi,

I have this table structure whereby I need to group "Member ID" column and sorted it alphabetically and combine it with a delimiter "-", and also the dependent column called "Member name" has to be sorted accordingly as Member ID. Currently, I only manage to group and sort Member ID, but missing "Member name" column after the group by action. Looking forward for your insights.

Requirement: Sorted Member ID alphabetically while GroupID and Group Desc are remained unchanged, and combine the text with delimiter "-", and then and sort Member name according to Member ID with the delimiter "-".

Input table:

Group ID	Group Desc	Member ID	Member Name
ABCDF	Team A	ASDD	George
ABCDF	Team A	DEEE	Ronaldo
ABCDF	Team A	FGHII	Beckham
DEFGH	Team B	DEEE	Ronaldo
DEFGH	Team B	HJKLLL	Victoria
DEFGH	Team B	ASDD	George

Expected output:

Group ID	Group Desc	Member ID	Member Name
ABCDF	Team A	ASDD-DEEE-FGHII	George-Ronaldo-Beckham
DEFGH	Team B	ASDD-DEEE-HJKLLL	George-Ronaldo-Victoria

[slorin · Accepted solution]

Hi

let
  Source = Your_Source
  Group = Table.Group(Source, {"Group ID", "Group Desc"}, {{"Data", each Table.Sort(_,{{"Member ID", Order.Ascending}})}}),
  Member = Table.AddColumn(Group, "Member ID", each Text.Combine([Data][Member ID],"-")),
  Name = Table.AddColumn(Member, "Member Name", each Text.Combine([Data][Member Name],"-"))
in
  Name

   Stéphane

[khom]

Hi @slorin this is exactly, what I'm looking for. This is a new insight for me to work with table. Thanks a lot!