Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Transform rows from certain columns to colunms

Hello all,

 

I have a very simple question. I have a very long table with many repatitive question. Here is an example:

 

I would like to tranform this table so as every question be under a unique column as follows:

 

Can please someone advice or send a similar case?

 

The values of the answers are both integer and text.

 

Please let me know.

 

Thank you,
Stavros

Need Help

2 Replies

  • Floriankx's avatar
    Floriankx
    Solution Sage
    8 years ago

    Hello,

     

    In Power Query it is

    let
    Source = Excel.CurrentWorkbook(){[Name="Questions"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", Int64.Type}, {"Question", type text}, {"Answer", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Question]), "Question", "Answer")
in
    #"Pivoted Column"
  • danextian's avatar
    danextian
    Super User
    8 years ago

    Hi Anonymous,

     

    In Power Query, do the following:

     

    • Click Question column. The values in this column be used as the names of the columns to be created.
    • Go to Transform tab then click Pivot Column. A dialogue box will appear.
    • Select Answer from the Values Column dropdown.
    • Click the arrow just before Advanced Option.
    • Clcik Don't Aggregate from the dropdown.
    • Then click OK.