Importing multiple choice data from SurveyMonkey

[Mariposa141]

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

[dufoq3]

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

[Mariposa141]

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!

[leo-giang-syd · Accepted solution]

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.

[Mariposa141]

This worked great, thanks so much for your help!

[leo-giang-syd]

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.