Forum Discussion

JPHoney's avatar
JPHoney
Regular Visitor
8 years ago

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 ...
BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
8 years ago

Hi There,

 

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

 

 

Thanks,

Bhavesh

JPHoney's avatar
JPHoney
Regular Visitor
8 years ago

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

 

  • Floriankx's avatar
    Floriankx
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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.