Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Hi @npatten,
It is indeed a Power Query transformation with Unpivot. See:
Hope it helps.
Regards,
Fernando
Hi @npatten,
It is indeed a Power Query transformation with Unpivot. See:
Hope it helps.
Regards,
Fernando
Thanks Fernando, it appears I was just over thinking the problem!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |