cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

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"

 

Thank you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

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

AlexisOlson_0-1635655529013.png

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"

 

Thank you.  This worked very well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors