Combining multiple values from single column into one row/record

[relentless226]

Hello:

I am working in PowerQuery/Transform within Power BI.  My table currently looks like the following- I have some customers with two sales people assigned and this creates issues in my visuals (a table visual right now) as I only want one row per customer:

Customer ID	Customer Name	Sales Person
111	Jones Manufacturing	John Smith
111	Jones Manufacturing	Jane Doe

What I would like to do is end up with one row for the rows above and concatenate the sales person's names together with an & between them in a custom column so I have something like this:

Customer ID	Customer Name	Sales People
111	Jones Manufacturing	John Smith & Jane Doe

I know this is probably pretty simple but I appreciate any help I can get!

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor with a custom Group By step.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJR8srPSy1W8E3MK01LTC4pLcrMSweLZuQpBOdmlmQoxeoQUJqYl6rgkp+qFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Customer Name" = _t, #"Sales Person" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Customer ID", "Customer Name"}, {{"SalesPerson", each Text.Combine([Sales Person], "&"), type text}})
in
    #"Grouped Rows"

Pat