What is the best way to sort this data in power query in PowerBI?

[Anonymous]

Hi all,

I could use some assistance. I have an excel file that looks something like this

ID	Start Time	End Time	Date	Store Number	Q1	Q2	Q3	Q4
1	9am	9pm	4/29	1	Pass	Fail	Pass	Fail

There are 200 questions. I want to sort the data to look something like this:

ID	Start Time	End Time	Date	Store Number	Question	Answer
1	9am	9pm	4/29	1	Q1	Pass
1	9am	9pm	4/29	1	Q2	Fail
1	9am	9pm	4/29	1	Q3	Pass
1	9am	9pm	4/29	1	Q4	Fail

Any idea of how to do this? Thank you.

[mahoneypat · Accepted solution]

Just select columns ID through Store Number (click ID, press shift and click on Store Number), then right click on one of the selected columns and choose 'Unpivot Other Columns'.  That should result in what you are looking for.

If that works, please mark this as the solution.  Let me know if it doesn't or if you have any questions.

Regards,

Pat