Group and Concatenate

[Renosaur]

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

[MarkLaf · Accepted solution]

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:

[Renosaur]

Spectacular. That worked. Thanks a million!