Forum Discussion

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Concatenate data across multiple columns

Hi,

This is the data

 

E-mail Title of the Keynote where the Question was asked  Question Status
A001 a as asw
A002 s cv der
A001 d vb ghn
A003 e nm mkl
A004 r yu opq
A003 t qp asp

 

As you can see, an email address appears multiple times in column A. I'd like to show each email only once in column A.  The expected result should concatenate the text entries of each of the other 3 columns in s single cell seperate by commas.  The result should look like this

 

E-mail Title of the Keynote where the Question was asked  Question Status
A001 a,d as,vb asw,ghn
A002 s cv der
A003 e,t nm,qp mkl,asp
A004 r yu opq

 

I'd like to solve this in the Query Editor.

 

Thank you for your help.

Need Help

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    You can use Group By and change the aggregation function to Text.Combine as I explain here.

    Full M code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUUoE4WIwUa4UqwMWNwJyQULJZUAiJbUIJg5SnwLEZUlAIj0jDyZuDOSmAnFeLpDIzc6BiZsAuUVAXFkKJPILCpHVlwBxYQHY3gKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E-mail" = _t, Title = _t, Question = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"E-mail", type text}, {"Title", type text}, {"Question", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"E-mail"}, {{"Title", each Text.Combine([Title],","), type text}, {"Question", each Text.Combine([Question],","), type text}, {"Status", each Text.Combine([Status],","), type text}})
in
    #"Grouped Rows"

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

    A more generic solution regardless of count or names of columns,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUUoE4WIwUa4UqwMWNwJyQULJZUAiJbUIJg5SnwLEZUlAIj0jDyZuDOSmAnFeLpDIzc6BiZsAuUVAXFkKJPILCpHVlwBxYQHY3gKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E-mail" = _t, Title = _t, Question = _t, Status = _t]),
    ColNames = List.RemoveItems(Table.ColumnNames(Source), {"E-mail"}),
    #"Grouped Rows" = Table.Group(Source, "E-mail", {"ar", each let content = Table.RemoveColumns(_, "E-mail") in Table.FromRows({List.Transform(Table.ToColumns(content), each Text.Combine(_, ", "))}, ColNames)}),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", ColNames)
in
    #"Expanded ar"

     

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      After defining ColNames, you could also do it in a single step:

       

      Table.Group(
    Source, {"E-mail"},
    List.Transform(
        ColNames,
        (col) => {col, each Text.Combine(Table.Column(_, col), ","), type text}
    )
)

       

       

      Full query:

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUUoE4WIwUa4UqwMWNwJyQULJZUAiJbUIJg5SnwLEZUlAIj0jDyZuDOSmAnFeLpDIzc6BiZsAuUVAXFkKJPILCpHVlwBxYQHY3gKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"E-mail" = _t, Title = _t, Question = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"E-mail", type text}, {"Title", type text}, {"Question", type text}, {"Status", type text}}),
    ColNames = List.Skip(Table.ColumnNames(#"Changed Type")),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"E-mail"}, List.Transform(ColNames, (col) => {col, each Text.Combine(Table.Column(_, col), ","), type text}))
in
    #"Grouped Rows"