Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
JPHoney
Regular Visitor

unpivoting survey data

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?

3 REPLIES 3
BhaveshPatel
Community Champion
Community Champion

Hi There,

 

It would be better if you can post the sample data and output expected.

 

 

Thanks,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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

 

Multichoice.JPG

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):

image.png

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.