Pivot/Unpivot Question

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 have 2?	Which fruits do you like 3?	How many do you have 2?
101	Apple	4	Plumb	8	Orange	14
102	Orange	3	Apple	2	Pear	9
103	Peach	5	Orange	2	Grapes	11
104	Grapes	2	Apple	3	Plumb	12

I'm trying to get to data like this:

101	Which Fruits	Apple	4
102	Which Fruits	Orange	3
103	Which Fruits	Peach	5
104	Which Fruits	Grape	5
101	Which 2	Plumb	8
102	Which 2	Apple	2
103	Which 2	Orange	2
104	Which 2	Apple	3
101	Which 3	Orange	14
102	Which 3	Pear	9
103	Which 3	Grapes	11
104	Which 3	Plumb	12

And finally to this (which is simply conditionally changing the Which 2 and 3's to which... so I think that's not hard)

101	Which Fruits	Apple	4
102	Which Fruits	Orange	3
103	Which Fruits	Peach	5
104	Which Fruits	Grape	5
101	Which Fruits	Plumb	8
102	Which Fruits	Apple	2
103	Which Fruits	Orange	2
104	Which Fruits	Apple	3
101	Which Fruits	Orange	14
102	Which Fruits	Pear	9
103	Which Fruits	Grapes	11
104	Which Fruits	Plumb	12

Any ideas?

Thanks!

doleary85 · Answer

Hi,&nbsp;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.&nbsp;If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

rnehrboss · Answer

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

doleary85 · Answer

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 👍

doleary85 · Answer

Sorry forgot the last part - add a new column to that table for which fruits:&nbsp;Column = "Which Fruits do You Like?"&nbsp;&nbsp;&nbsp;

Forum Discussion

Pivot/Unpivot Question

4 Replies

Related content

Pivot / Unpivot?

Pivot\Unpivot

Unpivot/Pivot

Pivot unpivot Timestamp Values

Unpivoting and Pivoting Rows