How do I pivot to desire format

[jinweitan]

Hi

I have a table that looks like this:

ID	Q1	Q2	Q1Comments	Q2Comments
1	Yes	Yes	Good	Excellent
2	Yes	Yes	Good	Excellent 2
3	No	No	Not so good	Bad
4	Yes	N/A	Okay	Not applied.

and would like to make it like this

ID	Questions	Answer	Comments	AnswerComments
1	Q1	Yes	Q1Comments	Good
1	Q2	Yes	Q2Comments	Excellent
2	Q1	Yes	Q1Comments	Good
2	Q2	Yes	Q2Comments	Excellent 2

.

.

.

Is there anyway possible to do that?

[nandukrishnavs]

@jinweitan 

Refer to the attached pbix file.

[amitchandak · Accepted solution]

@jinweitan , In power Query you have to unpivot

refer https://radacad.com/pivot-and-unpivot-with-power-bi

You may have to unpivot twice - https://kohera.be/blog/power-bi/how-to-unpivot-twice/

[Anonymous]

Hi @jinweitan ,

1. Click the "Transform data" button to get into the Power Query Editor page.

2. Choose the table on the left and right-click the ID column.

3. Choose Unpivot other columns.

You may need to duplicate your table and unpivot them separately.

Then using the merge function to get what you desired table.

Please try.

Aiolos Zhao