Table Transformation

[qhpowerbi]

Hi all,

I'm currently having difficulties doing table transformation on power query. I have a table as below;
Question, answer, target date and status are seperated on different column.

I would like to transform the table, so it look like as below where Question, answer, target date and status group in proper column

Please advice me on how to do that on tranformation panel. Appereciate you guys help.
Thank you.

[qhpowerbi]

Thanks @bcdobbs really appreciate your response 😀

[bcdobbs · Accepted solution]

1) Selected ID and Element and click Unpivot Other Columns. This gets:

2) Select new Attribute column and click "Column from Example" >> "From Selection". Then on first row type the number of the question. It should work out the text formula to do the rest (click the top of the column and rename to Question before pressing ok):

3) Select the Attribute column again and again click "Column from Example" >> "From Selection"

This time give an example of "Answer":

4) Use choose columns to remove Attribute.

5) Click the new Headings column and click Pivot. Select the Value column for Values Column, expand Advanced options and tell it not to aggregate.

6) Finished table: