Excel pivot table or pivot in Power Query to do this?

[kcmuppet]

Hi I'm trying to create a pivot table in structure shown in row 17 downwards (i.e. counting the number of users by response, but need to break it down by category and question.

I'm getting stuck because I can't work out how to arrange the pivot table to do that. I guess I need to transform my data first, but would appreciate some guidance on how to do that. Can anybody help?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
5	Source Data
6
7	User	Q1	Q1 Category	Answer 1	Q2	Q2 Category	Answer 2	Q3	Q3 Category	Answer 3	Q4	Q4 Category	Answer 4	Q5	Q5 Category	Answer 5
8	User 1	Age?	Physical	High	Height?	Physical	Low	Shoe Size?	Physical	Medium	Preferred food?	Preference	High	Preferred Colour?	Preference	High
9	User 2	Age?	Physical	High	Height?	Physical	High	Shoe Size?	Physical	Medium	Preferred food?	Preference	Low	Preferred Colour?	Preference	Low
10	User 3	Age?	Physical	Medium	Height?	Physical	High	Shoe Size?	Physical	Medium	Preferred food?	Preference	Low	Preferred Colour?	Preference	Low
11	User 4	Age?	Physical	Medium	Height?	Physical	High	Shoe Size?	Physical	High	Preferred food?	Preference	Low	Preferred Colour?	Preference	Medium
12	User 5	Age?	Physical	Medium	Height?	Physical	High	Shoe Size?	Physical	High	Preferred food?	Preference	Medium	Preferred Colour?	Preference	Medium
13	User 6	Age?	Physical	Low	Height?	Physical	Medium	Shoe Size?	Physical	Low	Preferred food?	Preference	Low	Preferred Colour?	Preference	Medium
14
15
16	Desired Output - ideally pivot table:
17		High	Medium	Low
18	Physical
19	Age?	2	3	1
20	Height?	4	1	1
21	Shoe Size?	2	3	1
22	Preference
23	Preferred food?	1	1	4
24	Preferred Colour?	1	3	2
Sheet2

Cell Formulas
Range	Formula
C19:E19	C19	=COUNTIFS(Data_Table[Answer 1],C$17)
C20:E20	C20	=COUNTIFS(Data_Table[Answer 2],C$17)
C21:E21	C21	=COUNTIFS(Data_Table[Answer 3],C$17)
C23:E23	C23	=COUNTIFS(Data_Table[Answer 4],C$17)
C24:E24	C24	=COUNTIFS(Data_Table[Answer 5],C$17)

 

[Skybluekid]

Is there a specific reason why you need a pivot table?

 

[Skybluekid]

If the answer is yes, you would need to rearrange your data, like below:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2		User	Question	Category	Answer		Count of Answer
3		User 1	Age?	Physical	High		Row Labels	High	Low	Medium
4		User 2	Age?	Physical	High		Physical	8	3	7
5		User 3	Age?	Physical	Medium		Age?	2	1	3
6		User 4	Age?	Physical	Medium		Height?	4	1	1
7		User 5	Age?	Physical	Medium		Shoe Size?	2	1	3
8		User 6	Age?	Physical	Low		Preference	2	6	4
9		User 1	Height?	Physical	Low		Preferred Colour?	1	2	3
10		User 2	Height?	Physical	High		Preferred food?	1	4	1
11		User 3	Height?	Physical	High
12		User 4	Height?	Physical	High
13		User 5	Height?	Physical	High
14		User 6	Height?	Physical	Medium
15		User 1	Shoe Size?	Physical	Medium
16		User 2	Shoe Size?	Physical	Medium
17		User 3	Shoe Size?	Physical	Medium
18		User 4	Shoe Size?	Physical	High
19		User 5	Shoe Size?	Physical	High
20		User 6	Shoe Size?	Physical	Low
21		User 1	Preferred food?	Preference	High
22		User 2	Preferred food?	Preference	Low
23		User 3	Preferred food?	Preference	Low
24		User 4	Preferred food?	Preference	Low
25		User 5	Preferred food?	Preference	Medium
26		User 6	Preferred food?	Preference	Low
27		User 1	Preferred Colour?	Preference	High
28		User 2	Preferred Colour?	Preference	Low
29		User 3	Preferred Colour?	Preference	Low
30		User 4	Preferred Colour?	Preference	Medium
31		User 5	Preferred Colour?	Preference	Medium
32		User 6	Preferred Colour?	Preference	Medium
Sheet3

 

[kcmuppet]

Thanks, yes reason for pivot table as a final result is I'd like to use slicers with it.

Now I need to try to work out the power query to rearrange the data....

 

[Skybluekid]

Is VSTACK an option? Or is it that you require it to be transformed from an external file?

Book1.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2		Age?	Physical	High	Height?	Physical	Low	Shoe Size?	Physical	Medium	Preferred food?	Preference	High	Preferred Colour?	Preference	High
3		Age?	Physical	High	Height?	Physical	High	Shoe Size?	Physical	Medium	Preferred food?	Preference	Low	Preferred Colour?	Preference	Low
4		Age?	Physical	Medium	Height?	Physical	High	Shoe Size?	Physical	Medium	Preferred food?	Preference	Low	Preferred Colour?	Preference	Low
5		Age?	Physical	Medium	Height?	Physical	High	Shoe Size?	Physical	High	Preferred food?	Preference	Low	Preferred Colour?	Preference	Medium
6		Age?	Physical	Medium	Height?	Physical	High	Shoe Size?	Physical	High	Preferred food?	Preference	Medium	Preferred Colour?	Preference	Medium
7		Age?	Physical	Low	Height?	Physical	Medium	Shoe Size?	Physical	Low	Preferred food?	Preference	Low	Preferred Colour?	Preference	Medium
8
9
10		Age?	Physical	High
11		Age?	Physical	High
12		Age?	Physical	Medium
13		Age?	Physical	Medium
14		Age?	Physical	Medium
15		Age?	Physical	Low
16		Height?	Physical	Low
17		Height?	Physical	High
18		Height?	Physical	High
19		Height?	Physical	High
20		Height?	Physical	High
21		Height?	Physical	Medium
22		Shoe Size?	Physical	Medium
23		Shoe Size?	Physical	Medium
24		Shoe Size?	Physical	Medium
25		Shoe Size?	Physical	High
26		Shoe Size?	Physical	High
27		Shoe Size?	Physical	Low
28		Preferred food?	Preference	High
29		Preferred food?	Preference	Low
30		Preferred food?	Preference	Low
31		Preferred food?	Preference	Low
32		Preferred food?	Preference	Medium
33		Preferred food?	Preference	Low
34		Preferred Colour?	Preference	High
35		Preferred Colour?	Preference	Low
36		Preferred Colour?	Preference	Low
37		Preferred Colour?	Preference	Medium
38		Preferred Colour?	Preference	Medium
39		Preferred Colour?	Preference	Medium
Sheet3

Cell Formulas
Range	Formula
L10:N39	L10	=VSTACK(L2:N7,O2:Q7,R2:T7,U2:W7,X2:Z7)
Dynamic array formulas.

 

[kcmuppet]

Thanks that's a good option/idea. My problem is that the actual data set I have is hundreds of noncontiguous columns, and thousands of rows and will be updated monthly. After setting up the vstack it doesn't really need to be dynamic, so probably figuring out a power query will be the most resource efficient way

 

[Skybluekid]

I did have a look at Power Query, but too my knowledge it might be very difficult. It may be an option to ask the question in the Power Tools section in the forum

 

[kcmuppet]

Just closing the loop: a power query solution is at this thread: Power Query to transform data ready for pivot