unpivoting survey data

[JPHoney]

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?

[BhaveshPatel]

Hi There,

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

Thanks,

Bhavesh

[JPHoney]

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]

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.