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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to concatenate Product Names with the same Product IDs.

Hi,

 

I can’t figure how to do this in Power Query. I have a table with two columns, Product_ID and Product_Name. It turns out that in some cases I have multiple Product_Name s for a given Product_ID.  What I want to do is convert these duplicates so Product_Name contains the concatenation of the product names. 

 

For example

 

Product_IDProduct_Name
1Apple
1Peach
2Cherry
1Orange

To

Product_IDProduct_Name
1Apple_Peach_Orange
2Cherry

 

Any ideas?

 

Thanks,

 

-Wes

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJVYrVgfACUhOTM8A8IyDPOSO1qKgSLulflJiXDlQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product_ID = _t, Product_Name = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Product_ID"}, {{"Count", each Text.Combine([Product_Name],"_"), type text}})
in
    #"Grouped Rows"

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Brilliant. Thanks so much.

Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJVYrVgfACUhOTM8A8IyDPOSO1qKgSLulflJiXDlQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product_ID = _t, Product_Name = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Product_ID"}, {{"Count", each Text.Combine([Product_Name],"_"), type text}})
in
    #"Grouped Rows"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors