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

  • BhaveshPatel's avatar
    BhaveshPatel
    Super 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
        Solution 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.