summarize data by cell values

[npatten]

Hi--

Trying to solve a summarization problem. I'm working with survey data for a client. Currently the data output is structed with questions as the column headers, and the cell values (converted from text to numerical) are the responses, something like this: 

Q1	Q2	Q3	Q4	Q5
1	1	1	0	0
0	1	1	1	1
1	1	0	0	0
0	1	0	1	1
1	0	0	0	1
0	1	1	1	0

However, my clients are very interested in stacked charts to review survey responses, so I need the results to be transformed into something like this:

	1	0
Q1	3	2
Q2	4	1
Q3	3	0
Q4	3	0
Q5	3	0

This does is not seem to be a transpose or unpivot problem (I've tried), but I suspect it's a sophisticated summarization problem. My DAX skills are decent but not amazing. I have created lots of tables using the SUMMARIZE function, but I just can't seem to wrap my head around this problem. Any help would be very appreciated. 

Thank you in advance!

[calerof · Accepted solution]

Hi @npatten,

It is indeed a Power Query transformation with Unpivot. See:

Unpivot all columnsShow in a matrix with Count

Hope it helps.

Regards,

Fernando

[npatten]

Thanks Fernando, it appears I was just over thinking the problem!