group by and concatenate

[Back2Basics]

Hi

I have a dataset with senstitive data, so I can't share it but I can try to explain it. 

There is a unique reference number. Each number can have many rows because there are the following columns 'act', 'purpose', 'activity'.  Each number can have multiple acts, each act can have multiple purposes and each purpose can have multiple activities. 

I'm trying to find a way of providing the data as one row, with with the data concantenated. I have done this for one of the columns by using the method in this YouTube video https://youtu.be/nJ7LzwiSwnw 

However, what I'd like to do is to provide the data across all the columns in this manner. 

Example:

reference	act	purpose	activity
1	A	A	A
1	A	B	A
2	B	A	A
2	B	A	B
2	A	B	A
2	A	B	B
3	A	B	A
3	A	B	B
3	A	B	C

And this would then be displayed lke this:

reference	act	purpose	activity
1	A	A, B	A
2	A	B	A, B
2	B	A	A, B
3	A	B	A, B, C

I thought about creating each column as separate tables and then joining them - but I wasn't sure this would work as some of the links across the different columns may be lost?

[goncalogeraldes · Accepted solution]

Hello there @Back2Basics ! I think the following link shows an example similar to what you need to do in your dataset:

https://exceloffthegrid.com/power-query-combine-rows-into-a-single-cell/

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Back2Basics]

Thanks for this @goncalogeraldes 

The article is useful but I don't think it's quite providing what Im after. This combines the data from two columns into one. I want to keep my three columns but to limit my number of rows per reference, but as you see from the example it won't always be 1 row per reference - could be, but might still be several rows but reduced at the same time. 

[goncalogeraldes]

@Back2Basics halfway through the article, the user explains how to combine two or more rows of data into one and separating them by a comma as you need. It is in the "Combine rows into a single cell" chapter, please check the contents index for hyperlink

[Back2Basics]

I've got it now, thanks to this link. It isn't exactly like the example but figured out that if I did 3 sets of 'group by' then I would get what I wanted. 

So I grouped 'activity' by reference, act, purpose - by sum of activity. Then used the tip in the article to get the text.

Then I grouped 'purpose' by reference, act, sum_activity - by sum of purpose + formula change.

Finally, grouped 'act' by reference, sum_purpose, sum_activity - by sum of act + formula change.

This is now exactly how I've been asked to provide it. Thanks for the article link