Combining Rows based on the ID of that table

[Adam01]

Hello!

I am wanting to combine values based on the unique ID of that table, so for any values that have the same ID I want them to be pushed into the same cell. To explain this better I've included 2 screenshots labelled Old and New. Would I use Power Query or something to do with pivoting or grouping columns rows based on a column value? Unsure where to start 

 Old (Table I have currently)

 New (Table I would like to have)

[lynnzrae]

I did this and everything looked great (the top row of data was combined and concatenated).  I refreshed my data and had a new row that needed to be brought in to the existing merged row b/c it had the same ID and it didn't bring it in.  It now has a separate row.  

The 7332 is the key. this is what it currently looks like with the first row being the result of the inital grouping:

ID	P Number	Store Number	File Number	Names of Product
7332	6325	7	3921, 34749	Teflon Foot, Compensating Foot
7332	6325	7	3925	Piping Foot

This is what it should look like based after today's data refresh:

ID	P Number	Store Number	File Number	Names of Product
7332	6325	7	3921, 34749, 3925	Teflon Foot, Compensating Foot, Piping Foot

Do I have to regroup every time I have a data refresh?

[Syndicate_Admin]

Hello Syndicate_Admin

Can this be done twice or more? Can you Group by ID and Date and Order Number all at once?

[AlexisOlson · Accepted solution]

You can do this with a small tweak to Group By.

Click Group By under the Home tab and group by ID taking the max over Value.

This generates code that looks like this:

= Table.Group(#"Changed Type", {"ID"}, {{"Value", each List.Max([Value]), type nullable text}})

We don't actually want List.Max though. Replace that with Text.Combine like this:

= Table.Group(#"Changed Type", {"ID"}, {{"Value", each Text.Combine([Value], ", "), type text}})

[Anonymous]

Perfect! Thank you!

[chanpreet_90]

how to combine if the column has text and number both?

[Adam01]

Thank you! This is exactly what I needed