Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |