Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

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

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

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"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors