Forum Discussion

vishal17081990's avatar
vishal17081990
Helper I
8 years ago
Solved

Need help on power query

Hi All,

 

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

 

Column1.q1.questionColumn1.q1.optionsColumn1.q1.answerColumn1.q2.questionColumn1.q2.optionsColumn1.q2.answerand so on…
5 + 7 = ?10,11,12,131212 - 8 = ?1,2,3,44and so on…

 

i want to transform it as below in power query:

 

QuestionOptionsAnswer
5 + 7 = ?10,11,12,1312
12 - 8 = ?1,2,3,44
12 / 4 = ?1,2,3,43

 

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

 

Regards,

VIshal.

 

2 Replies

  • drewlewis15's avatar
    drewlewis15
    Solution Specialist
    8 years ago

    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: