Forum Discussion
Importing multiple choice data from SurveyMonkey
Hi everyone,
I'm trying to import multiple choice data collected via SurveyMonkey into Power BI (our SurveyMonkey plan doesn't include the Power BI connector). I'm pretty new to Power BI and I'm struggling to import it. I wondered if someone in the community might be able to help?
Many thanks in advance for any suggestions!
My data looks something like this:
| Respondent ID | Which of the following activities do you carry out at least once per month? (Select all that apply) | |||||||
| 1 | Physical exercise | Meditation | Listening to music | Reading | Eating out | Cinema | ||
| 2 | Physical exercise | Computer games | Listening to music | Reading | Eating out | Cinema | ||
| 3 | Cinema | |||||||
| 4 | Physical exercise | Meditation | Listening to music | Reading | Eating out | Cinema | ||
| 5 | Physical exercise | Listening to music | ||||||
| 6 | Physical exercise | Computer games | Eating out | |||||
| 7 | Physical exercise | Computer games | ||||||
| 8 | Listening to music | Reading | ||||||
| 9 | Physical exercise | Reading |
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.
5 Replies
In this case, you need to download the datasets in excel, csv, PDF or similar formats then you can import them into power query (Data tab --> Get data --> choose datasource format ...), instead of feeding data directly from that website.
Whenever you need the update the data, please save the new dowloaded file, and then refresh the respective query in power query or simply click "refresh all" in Data tab.
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!
Hi Mariposa141, for future requests, provide also expected result based on sample data please.
This approach is similar to leo-giang-syd, but dynamic.
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVFNSwNBDP0rYU8Kvaj16+SheigoSD14WHoIs2knMDNZdrLq/nvTWqkt3ZYWhAnk673kZcqymFCuJVWUFMaPxaB49+w8yAzUE8wkBPnkNAd0yh+sTNl6tt90UBaVQCctOGyaDqRVQIVAmBUkOYKaGoiS1D/swK8ozt4okDNkCDbd8FjXoTu3MvTaAndh3qvvMjsMQF/UOM606nihihWVJVnwzFkpLeSoQGwNYMkJYWUp856sz2q2vAUjThRxyX/Zwz+SWLdqwuYYl3eB02dc7VH5p234z1Kv9/D3qtv8jZtjrvVrG/v88NyewrNe424re+Aga+D9gRPALtz0Gw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]), RemovedTopRowsDynamic = Table.Skip(Source, each [Column1] <> "1" and [Column1] <> 1), TrimmedTextDynamic = Table.TransformColumns(RemovedTopRowsDynamic, List.Transform(Table.ColumnNames(RemovedTopRowsDynamic), (colName)=> {colName, each if Text.Trim(_) = "" then null else Text.Trim(_)})), UnpivotedOtherColumns = Table.UnpivotOtherColumns(TrimmedTextDynamic, {"Column1"}, "Attribute", "Answer"), RemovedColumns = Table.RemoveColumns(UnpivotedOtherColumns,{"Attribute"}), RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Column1", "Respondent ID"}}) in RenamedColumns
