Need help on power query

[vishal17081990]

Hi All,

i am having data as below in single row for multiple questions:

Column1.q1.question	Column1.q1.options	Column1.q1.answer	Column1.q2.question	Column1.q2.options	Column1.q2.answer	and so on…
5 + 7 = ?	10,11,12,13	12	12 - 8 = ?	1,2,3,4	4	and so on…

i want to transform it as below in power query:

Question	Options	Answer
5 + 7 = ?	10,11,12,13	12
12 - 8 = ?	1,2,3,4	4
12 / 4 = ?	1,2,3,4	3

is there any way we can do this in power query?

Regards,

VIshal.

[drewlewis15 · Accepted solution]

First, select all columns and choose to "Unpivot Columns" (found on the Transform tab).  This will unpivot your table to look like this:

From here, split your Attribute column by delimiter (.) to get the question number and the category into separate columns (you can then simply delete the column that references "Column1" in all rows.  Should end up looking like this:

Finally, select your "Attribute.3" column (or whatever the column name ends up being for your categories: question, options, answer) and choose to Pivot the table by the "Value" column along with choosing not to aggregate.  See screenshot below:

This should give you your desired result:

[drewlewis15 · Accepted solution]

First, select all columns and choose to "Unpivot Columns" (found on the Transform tab).  This will unpivot your table to look like this:

From here, split your Attribute column by delimiter (.) to get the question number and the category into separate columns (you can then simply delete the column that references "Column1" in all rows.  Should end up looking like this:

Finally, select your "Attribute.3" column (or whatever the column name ends up being for your categories: question, options, answer) and choose to Pivot the table by the "Value" column along with choosing not to aggregate.  See screenshot below:

This should give you your desired result:

[vishal17081990 · Accepted solution]

@drewlewis15 perfect!!!! 🙂

looks great.... Thanks a ton... (y).