Forum Discussion
Combining multiple values from single column into one row/record
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!
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
1 Reply
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