Removing duplicates by merging rows into one column

[Styx]

Hello,

I have a table that looks like the following:

Item	Promotion	Status
X	Halfprice	Active
X	On POS	Active
Y	$9.99	Ongoing

What I need to do is remove the duplicate Item values but keeping the Promotion names. I was hoping I could find a way to merge each Promotion into one column for each distinct Item.

EDIT: Like so:

Item	Promotion
X	Halfprice|On POS
Y	$9.99

Is there a way to do this?

[Styx · Accepted solution]

A colleague was able to provide me with a solution as follows:

• Group on your Primary Key and aggregate as All Rows
• Add Column using Table.Column([Count],"Promotion") ([Count] being the name of the aggregation column)
  • This will create a List column
• Extract Values from List
• Remove the Group column