Hello!
I need a little bit of guidance for how I can get survey responses that are setup as a Likert Scale matrix into a layout that I can then easily utilize in Pivot Tables and charts. I get close with Pivot/Unpivot in PowerQuery but I'm not sure I'm doing it the best way it can be done. Ultimately the data will be used in scatter graphs setup in 4 quadrants (I have that part down fine).
I've attached the sample file. Right now the full data set is giving me over 150 columns to work through.
Any suggestions are appreciated!
Thank you!
Mamie
Hi Mamie,
Looks like a survey monkey download, is that correct?
In this case, you can try the SurveyMacros attached. Found it on a forum a while ago (not sure which one to post the link).
Hi Catalin,
You're correct - definitely a Survey Monkey download. 😀
Thanks for the macro. That was helpful, and I can certainly work with what it gave me. Unfortunately, the only parts that worked for me were the "Data Transform" and the "Make Pivot Tables" macros. The others gave me errors that I don't know how to fix - which is really sad because I was really looking forward to getting all of the questions onto one tab. 🙁
What I need to get is the questions all in one column with the A-D responses as their own columns (just like it would have been setup on the survey, but consolidated) without generating hundreds of rows or columns per respondent. There are 39 questions I have that this applies to and then 10 that are open-ended responses.
I have Pivoted/Unpivoted the data in Power Query and have gotten close (see image attached) but I know I am still missing something because for my 10 test responses I have 380 rows. I'm thinking I may need to do more Pivot/Unpivot work but not sure what columns to apply those steps to.
Ultimately I will be using this data to create a chart like below.
Hi Mamie,
If you can provide an example of the data you have, before any transformation and how it should look like after transformation (a manual example), it will be much easier to see what you need.
Hi Catalin,
I will try to get those loaded later today. I'm sure you've got an idea or two how to make it less manual! 😀
While the macro provided didn't fully work, it DID give me the idea to separate the numerical responses from the open-ended responses and that helped me get it from SurveyMonkey's format into something much more useful using PowerQuery and PivotTables that I could then build charts with.
Okay, now I can share what I did so far.
I took the exported data from SurveyMonkey and made it a table. Then I put the table into PowerQuery. In PowerQuery, I:
- Unpivoted the data so my questions were all in one column and the answers were all in another and removed all open-ended responses.
- Split the question column so I could get the actual question and the rating type (A, B, C, D) into another column.
- Pivoted the data so the rating types were in one column and the actual questions were now my header row.
- Loaded this into a new table on a new worksheet.
- Created Pivot Tables for each question so I could get the averages of each response type.
Created a new table with the question number, question name, question type, and average of each response type.
I used the data in the Pivot Tables for each question type entry.
This got the data into the format I needed it in to create the scatter graphs for a Hennessy-Hicks Training Needs Analysis.
I've attached a sample file with the Starting Data, Formatted Data, and Chart Result I need. Any ideas to make this a less manual process are welcome! Bonus if they aren't VBA or Data Model driven. I have NO skill with VBA and the client I'm doing this for uses a Mac which apparently doesn't play very well with the Data Model. 😀
