Forum Discussion
Solved
Power Query - Need help to group and sort column Member ID and sort column Member Name accordingly
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 |
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
NameStéphane
2 Replies
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
NameStéphane
