Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have exported survey data from a survey tool (Qualtrics) and the grid questions and multiple choice questions need to be unpivoted. I have been able to do that by duplicating the datsheet and removing all other columns excepth that particular question and then unpivot. The problem is, by doing that I then have multiple datasheets and I can\t analyse these questions by other questions in the survey. Can anyone help with how I can unpivot the grid and multiple choice questions all in the one datasheet?
Hi There,
It would be better if you can post the sample data and output expected.
Thanks,
Bhavesh
thanks Bhavesh,
below is an example where a respondent has answered a multichoice question (attributes 1 - 6). The first 3 cols are just an example of all the other data that I have from the survey. If I unpivot the attribute columns to be in a format that I can report on, the datasheet becomes too large because the unpivot needs to be for all the other variables as well. To get it to work, I have saved as a seperate datasheet just the attribute cols and unpivoted but then I don't have the ability to analyse by the other factors
Hello, what is your exact aim?
For Unpivot you should do:
let Source = Excel.CurrentWorkbook(){[Name="Survey"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", Int64.Type}, {"Sex", type text}, {"Prior Activity", type text}, {"A1", Int64.Type}, {"A2", Int64.Type}, {"A3", type any}, {"A4", Int64.Type}, {"A5", Int64.Type}, {"A6", Int64.Type}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"A1", "A2", "A3", "A4", "A5", "A6"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Age", "Sex", "Prior Activity"}, "Attribut", "Answer") in #"Unpivoted Other Columns"
I replaced empy cells by 0 but this is optional depending on how you want to treat your dataset afterwards.
This is the result (extract):
Depending on the number of participants I understand your rows multiply by the number of attributes but Power Querry should be able to handle this easily.
Best regards.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |