Forum Discussion
Importing multiple choice data from SurveyMonkey
So, You have to import the data as I suggested earlier first. Then the below transformation steps must be taken in Power Query Editor before loading to excel:
1. At the beginning, Power Query Editor looks like this:
2. make row #1 as column header: Home --> transform group --> choose "user First Row as Header"
3. filter the first column and untick (null) which means "blank cell":
4. rename the "answer" columns (#2 to #8) to match with the activity option names
5. unpivot those answer columns (#2 to #8): choose those columns --> right click mouse on any chosen cells --> select "unpivot column"
6. You can delete either "Attribute" or "Value" column. Now you already have the wanted result with the respondent IDs (which repeat multiple times depending on how many activities chosen) and their corresponding answers - you can randomly check IDs 1 & 3 which meet your demand.
If you think my suggestion is helpful, please give me a "kudo" thanks.
Thanks for the reply. I realised my question wasn't very clear - at the moment the multiple choice answers are spread across multiple columns, as in the data above. I would like to import the data as a single question so that I can easily analyse the findings. Thank you!
So, You have to import the data as I suggested earlier first. Then the below transformation steps must be taken in Power Query Editor before loading to excel:
1. At the beginning, Power Query Editor looks like this:
2. make row #1 as column header: Home --> transform group --> choose "user First Row as Header"
3. filter the first column and untick (null) which means "blank cell":
4. rename the "answer" columns (#2 to #8) to match with the activity option names
5. unpivot those answer columns (#2 to #8): choose those columns --> right click mouse on any chosen cells --> select "unpivot column"
6. You can delete either "Attribute" or "Value" column. Now you already have the wanted result with the respondent IDs (which repeat multiple times depending on how many activities chosen) and their corresponding answers - you can randomly check IDs 1 & 3 which meet your demand.
If you think my suggestion is helpful, please give me a "kudo" thanks.
This worked great, thanks so much for your help!