How to unpivot and pivot multiple columns into their own groups with their respective rows?

[ninos-shiba]

I have a table in this structure (from a SharePoint List):

Row ID

Date

Evaluator Name

Question 1

Question 2

Question 3

Question 1 Comments

Question 2 Comments

Question 3 Comments

Question 1 Point

Question 2 Point

Question 3 Point

Question 1 Missed Reasons

Question 2 Missed Reasons

Question 3 Missed Reasons

1

9/18/2020

Jake

Good

Bad

Good

Comments here

Comments here

Comments here

3

1

3

Reason 1, Reason 2

Reason 2

Reason 2, Reason 4

2

9/19/2020

Mary

Bad

Good

Good

Comments here

Comments here

Comments here

1

3

3

Reason 1

Reason 2

Reason 3

3

9/23/2020

William

Neutral

Neutral

Good

Comments here

Comments here

Comments here

2

2

3

Reason 2

Reason 2

Reason 2

4

9/23/2020

Jake

Bad

Good

Bad

Comments here

Comments here

Comments here

1

3

1

Reason 3, Reason 4

Reason 4

Reason 1

I'd like to transform it (unpivot, pivot, etc.) to obtain this result (for example):

Row ID	Date	Evaluator Name	Question	Response	Comments	Points	Missed Reasons
			Question 1	Good	Question 1 Comments
			Question 1
			Question 1

Basically, I'd like to group all the question columns, comments columns, points columns, and reason columns into their own respective columns with the corresponding rows.

Is there a way to transform my data to get that end result?

[AllisonKennedy · Accepted solution]

You will need to find the delimiter between Question 1 and Comments, Points, etc.

For example, if you have Question 1: Comments
Question 1: Points
Question 1: Missed Reasons

If you don't have that delimiter, you can use a conditional column to look for text that contains Missed Reasons, Point, Comments and use that to determine which part of the question it answers.

Please see the attached file for reference. I will upload it under the signature.