Forum Discussion

relentless226's avatar
relentless226
Regular Visitor
5 years ago
Solved

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 visua...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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