Forum Discussion

rnehrboss's avatar
rnehrboss
Icon for Helper II rankHelper II
3 years ago

Pivot/Unpivot Question

Having trouble thinking through this pivot/unpivot question transform. I have data like this: ID Which fruits do you like? How many do you have? Which fruits do you like 2? How many do you h...
Need Help
DOLEARY85's avatar
DOLEARY85
Icon for Resident Rockstar rankResident Rockstar
3 years ago

Hi,

 

you could duplicate the table a couple of times and remove 2 of the which friuts and how many columns in each.

Rename all the columns to remove numbering and so they are the same in each table and then append them together, then just unpivot the which fruits column.

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

  • rnehrboss's avatar
    rnehrboss
    Icon for Helper II rankHelper II
    3 years ago

    A great idea.  Problem is I have about 30 groups of these with 10 each.. So might become quite cumbersome.

    • DOLEARY85's avatar
      DOLEARY85
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      Ah okay, you could create a calculated table with DAX in the data view:

       

      t2 =
      UNION(SUMMARIZE('Table (6)','Table (6)'[ID],'Table (6)'[Which fruits do you like?],'Table (6)'[How many do you have?]),SUMMARIZE('Table (6)','Table (6)'[ID],'Table (6)'[Which fruits do you like 2?],'Table (6)'[How many do you have 2?]),(SUMMARIZE('Table (6)','Table (6)'[ID],'Table (6)'[Which fruits do you like 3?],'Table (6)'[How many do you have 2?.1])))
       

       

      If I answered your question, please mark my post as solution, Appreciate your Kudos 👍