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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors