cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kristenmcnelly
Frequent Visitor

Concatenating field from different rows with same ID

I have a table like this

IDName
1Anne
1Bob
2Cara
2Doug
3Ed
3Fannie

 

I want the table to look like this

IDNames
1Anne, Bob
2Cara, Doug
3Ed, Fannie
1 ACCEPTED SOLUTION
slorin
Solution Sage
Solution Sage

Hi

Group Column, choose "Sum" and replace List.Sum by Text.Combine

= Table.Group(Source, {"ID"}, {{"Data", each List.Sum([Name]), type nullable text}})
= Table.Group(Source, {"ID"}, {{"Data", each Text.Combine([Name],", "), type nullable text}}) 

 Stéphane

View solution in original post

3 REPLIES 3
slorin
Solution Sage
Solution Sage

Hi

Group Column, choose "Sum" and replace List.Sum by Text.Combine

= Table.Group(Source, {"ID"}, {{"Data", each List.Sum([Name]), type nullable text}})
= Table.Group(Source, {"ID"}, {{"Data", each Text.Combine([Name],", "), type nullable text}}) 

 Stéphane

That worked! Thank you so much!

jgeddes
Solution Sage
Solution Sage

There are a few ways to do this.
Something like this will work...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lHyS8xNVYrViVYyBHIc8/IQHKf8JDDbCMh2TixKhHNc8kvTwRxjIMc1Bc50S8zLywTqjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID"}, {{"Names", each _, type table [ID=nullable text, Name=nullable text]}}),
Custom1 = Table.AddColumn(#"Grouped Rows", "namesOnly", each Combiner.CombineTextByDelimiter(", ")(Table.Column(Table.SelectColumns([Names], "Name"), "Name"))),
#"Removed Columns" = Table.RemoveColumns(Custom1,{"Names"})
in
#"Removed Columns"

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors