Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to groupby within columns

i have a table with data as below :

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

 

placesA001_MangoA011_orangeA201_grapesTypeID
TexasAvailableAvailable Oragnic fruitA0001
VirginiaAvailableAvailable Oragnic fruitA0001
MDAvailable AvailableOragnic fruitA0001
DCAvailableAvailable Oragnic fruitA0001
VAAvailableAvailableAvailableOragnic fruitA0001
CAAvailableAvailable Regular FruitB0001
NJAvailableAvailable Regular FruitB0001
NYAvailable AvailableRegular FruitB0001
DEAvailableAvailable Regular FruitB0001
PAAvailableAvailableAvailableRegular FruitB0001

Expected Results:

 

A001_MangoA011_orangeA201_grapesTypeID
AvailableAvailableAvailableOragnic fruitA0001
AvailableAvailableAvailableRegular FruitB0001
Need Help
Show and Tell

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        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.⚡