locked
Pivot Values to Single Concatenated Value RRS feed

  • Question

  • Hi All,

    I'm struggling to figure this one out.   I'd like to "Pivot" values in the Input table below, but Concatenate the values into a single value.  Any ideas?  Thank you in advance! 

    Input
    Key Value
    5604 Hotel
    5604 Door
    5604 Elevation
    5604 Corporation
    5604 AL
    6208 Twenty
    6208 Five
    6208 Consulting
    7259 Various
    7259 Naming
    7259 Methodologies
    7259 Inc
    7259 Reston
    7259 VA

    Output
    Key Concatenated Name
    5604 Hotel Door Elevation Corporation
    6208 Twenty Five Consulting
    7259 Various Naming Methodologies Inc Reston VA

    Wednesday, August 16, 2017 11:31 PM

Answers

  • You'd better use "Group By" and group on Key. Now you need to choose some operation for the Value column to have some code generated (e.g. Max) and then adjust the generated code by adjusting List.Max into a Text.Combine formula.

    let
        Source = Table1,
        #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Concatenated Name", each Text.Combine([Value]," "), type text}})
    in
        #"Grouped Rows"

    Thursday, August 17, 2017 12:18 AM

All replies

  • You'd better use "Group By" and group on Key. Now you need to choose some operation for the Value column to have some code generated (e.g. Max) and then adjust the generated code by adjusting List.Max into a Text.Combine formula.

    let
        Source = Table1,
        #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Concatenated Name", each Text.Combine([Value]," "), type text}})
    in
        #"Grouped Rows"

    Thursday, August 17, 2017 12:18 AM
  • Marcel, you are the Table.Group master:  each of your posts makes us discover new powers of the Table.Group.

    Thank you

    Thursday, August 17, 2017 3:59 AM
  • Bingo, worked exactly as needed.  Thank you Marcel!

    Thursday, August 17, 2017 2:29 PM