Forum Discussion
Report: grouped by rows & columns (is this possible?)
Greetings everyone - report builders especially, I have a batch of survey responses (Answer Text) and I'd like to view them grouped by users (rows) & questions (column headings). Is this possible to...
And I have used Power Query before to help solve a different Excel problem. It's a great plug-in!
stevep17 Something like in the attached file perhaps. First a bit of cleaning up in PQ, then some Power Pivot.
- Yes! That's it exactly.
We're hoping to get to this with the click of a button...if that's possible (a macro)?
We need a regular non-power user to be able to take the raw survey report and transform it easily to this end result.stevep17 The easiest would be to connect to the file with the raw data, and then click "Refresh All" on the Data ribbon. That will connect to the data, transform it, load it into the data model, and update the pivot table. Personally I would not go so far as to write VBA code just to refresh.
But make sure that the data is in a structured table and clean, i.e. get rid of the empty column B, no merged cells, no fancy formatting needed.
The user doesn't need advanced skills to use such a schedule and the advantage it that the raw data is not touched/changed in any way. Since you have used PQ before, you'll know what I mean.