How to groupby within columns

[Anonymous]

i have a table with data as below :

Please help how can i do this scenario. The column names are dynamic and changes everymonth.

places	A001_Mango	A011_orange	A201_grapes	Type	ID
Texas	Available	Available		Oragnic fruit	A0001
Virginia	Available	Available		Oragnic fruit	A0001
MD	Available		Available	Oragnic fruit	A0001
DC	Available	Available		Oragnic fruit	A0001
VA	Available	Available	Available	Oragnic fruit	A0001
CA	Available	Available		Regular Fruit	B0001
NJ	Available	Available		Regular Fruit	B0001
NY	Available		Available	Regular Fruit	B0001
DE	Available	Available		Regular Fruit	B0001
PA	Available	Available	Available	Regular Fruit	B0001

Expected Results:

A001_Mango	A011_orange	A201_grapes	Type	ID
Available	Available	Available	Oragnic fruit	A0001
Available	Available	Available	Regular Fruit	B0001

[parry2k]

@Anonymous you should unpivot your table

- transform data
- select places, type, id
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add type and id on rows,
- add attribute on columns
- add value on values section

on format page, turn off step layout, and drill down to next level and that will get what you are looking for, it might require small tweaking but it will get what you want.

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!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Thanks a lot for your response. it works fine on visual page but i want it in table later i need to merge with another table.

[v-easonf-msft]

Hi , @Anonymous 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[parry2k]

@Anonymous sure whatever you like to do. It is ready for further use of it. It answered your original question (I believe), if you have more question, create another post.

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!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡