Concatenate data across multiple columns

[Ashish_Mathur]

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.

[CNENFRNL]

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"

[Ashish_Mathur]

Thank you.

[AlexisOlson]

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"

[AlexisOlson]

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"

[Ashish_Mathur]

Thank you.  This worked very well.