Forum Discussion
Group and Concatenate
I'm a new user and struggling to find a way to do this through various ways of grouping and concatenating for my desired result (and AI has been no help!). I can usually work things out but getting desparate with this one.
I have a spreadsheet with manager names in column A, their email addresses in column B and direct report in column C.
I want to concatenate the direct report names so each manager name appears only once and all their direct reports are concatenated into a cell with a comma delimiter.
Current state:
| Manager name | Manager email | Direct report |
| Bob Brown | bob@jones | Sarah L |
| Bob Brown | bob@jones | Kyle N |
| Bob Brown | bob@jones | Bob B |
| Bob Brown | bob@jones | Renee Z |
| Nick Cave | nick@cave | Polly H |
| Nick Cave | nick@cave | Henry L |
Desired state
| Manager name | Manager email | Direct report |
| Bob Jones | bob@jones | Sarah L, Kyle N, Bob B, Renee Z |
| Nick Cave | nick@cave | Polly H, Henry L |
This can be done with Group By and customizing the aggregation that gets performed. Instead of returning all rows or for example a Min agg, we can use Text.Combine. Given the table you provided, the M in this step would give you your desired output
= Table.Group(Source, {"Manager name", "Manager email"}, {{"Direct Report", each Text.Combine( [Direct report], ", " ), type nullable text}})Output with test data:
2 Replies
This can be done with Group By and customizing the aggregation that gets performed. Instead of returning all rows or for example a Min agg, we can use Text.Combine. Given the table you provided, the M in this step would give you your desired output
= Table.Group(Source, {"Manager name", "Manager email"}, {{"Direct Report", each Text.Combine( [Direct report], ", " ), type nullable text}})Output with test data:
Spectacular. That worked. Thanks a million!
