Unpivot Suvey Data from SharePoint List

[Steve_AAA]

I have a SharePoint list that has containing survey data - columns like person filling in the survey (Microsoft Form), the date and details. I have a column for each question answer and question comment e.g.

Name	Date	Question 1 Answer	Question 1 Comments	Question 2 Answer	Question 2 Comments	Question 3 Answer	Question 3 Comments	Question 4 Answer	Question 4 Comments
Mickey Mouse	04/10/23	Yes	example comment goes here	100	This area could be better	10	n/a	Yes	this is brilliant
Donald Duck	25/12/2022	No		50	sometimes	1	room for improvement	No	this could be better

How can I unpivot this so that I get a line per question, with an answer and comment column?

Name	Date	Question	Answer	Comment
Mickey Mouse	04/10/2023	Question 1	Yes	example comment goes here
Mickey Mouse	04/10/2023	Question 2	100	This area could be better
Mickey Mouse	04/10/2023	Question 3	10	n/a
Mickey Mouse	04/10/2023	Question 4	Yes	this is brilliant
Donald Duck	25/12/2022	Question 1	No
Donald Duck	25/12/2022	Question 2	50	sometimes
Donald Duck	25/12/2022	Question 3	1	room for improvement
Donald Duck	25/12/2022	Question 4	No	this could be better

[christinepayton]

Hold control and select the first two columns, right click, then "unpivot other columns". Then split the "Attribute" column on something that will split that last word into its own column (the answer/comment word) - I split on number of characters, since the position is always in the same place in the example. 

That will put the question number in one column and Answer/Comment label in another. Then Pivot that Answer/Comment column again, choosing "do not aggregate" under advanced and the values column for the values dropdown. That will make Answer/Comment into separate columns and pivot the values under them:

[mussaenda]

Hi @Steve_AAA 

[mlsx4]

Hi @Steve_AAA 

Assuming your data is in this way (it is not very clear in your explanation):

You just have to select Type column and go to Transform > Pivot column

And then, you pivot over Answer and in advanced options: not summarize

[Steve_AAA]

I've amended my original message to make things clearer. I currently have one row per survey response, but I want to have a row per question of the survey response, with a question, answer and comment column for each. I am using the unpivot function for just question and answer at the moment, but there is a new requirement to add comment to each line, so I am trying to amend the logic. 

[mlsx4]

Okey, @Steve_AAA I have it clearer now.

Then you need to include previous steps to the ones I have told you before.

1) Select Name and date and unpivot other columns

2) You will get two columns called Attribute and Value

3) Split Attribute by delimeter (space) but the most on the right

4) Rename your columns to Question, Type and Value

5) Then, Pivot column as I told you before.

If you have some doubt, please write me back