Transforming columns into row values

[SimonMalberg]

Hi,

I am doing a Net Promoter Score report were our customers are being asked different questions.

In six of the questions they can write comment.

In my dataset all questions is a column, and the answer the to question are the rows.

I would like to get the subject of the question and transform it into a row value before the answer to the question in a new column called Comment category.

At this moment all I can figure is to filter the six questions into six different tables. But i need to export this to excel afterwards for serveral differents routes.(working with ferries)

1. image is what i have now.

Additional info: all six tables are basically the same except the last column which differs in the question the customer is being asked which is optional to answer. I would like column no. 4 to be merged into one column. The tricky part is the customer can comment in serveral questions, so to concenate is not preferable.

2. image is what i prefer it to look like without having to do too much manual work, where column 4 (Kategori) is the question right before column 5(the answer to the question)

I hope I have explained my question in an understandable way and one of you might be able to help me.

Best,

Simon

[krishb1414]

hi @SimonMalberg  You can merge all those tables by clicking Merge at top of ribbon and select columns you want to unpivot then click on UNPIVOT.

See the images below for your reference.

[DOLEARY85 · Accepted solution]

Hi,

in power query select all the question columns, right click and unpivot them, this will put all the questions in one column and the answers in the column next to them.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍