cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aitenlabs
Regular Visitor

group all values from multiple columns into one

Hi all,

I have an excel file to track activities. All of those activities have one or more categories, the problem is that I need to list all activities and all of their categories but at the moment they are split between multiple columns.

 

Is it possible to group multiple columns into one, keeping the relationthip with the activity ID? 

I mean is it possible to "create" a new table that has each rows as the number of categories?

 

Like
ID    |    Category

1     |     cat1

1     |     cat 2

2     |     cat 3

 

Annotazione 2020-04-27 161324.png

 
 

 

 

Hope my question is understandable XD

thanks in advance

 
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@aitenlabs just unpivot your table in the power query.

 

- click transform data

- select id column 

- right click and select unpivot other column

 

you will get two new columns, attribute and value

 

- rename attribute to category

 

and now you can visualize the data by Id and category. Hope it helps.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@aitenlabs just unpivot your table in the power query.

 

- click transform data

- select id column 

- right click and select unpivot other column

 

you will get two new columns, attribute and value

 

- rename attribute to category

 

and now you can visualize the data by Id and category. Hope it helps.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Thanks @parry2k  🙏🙏🙏 

I discovered a new "thing" I wans't aware of 😁😁😁

 

happy quarantine 🙂

@aitenlabs beautiful, glad to hear. Cheers!!

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.