Forum Discussion
Combining Rows based on the ID of that table
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)
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}})
6 Replies
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}})Thank you! This is exactly what I needed
how to combine if the column has text and number both?
Hello Syndicate_Admin
Can this be done twice or more? Can you Group by ID and Date and Order Number all at once?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?
