Transform rows from certain columns to colunms

[Anonymous]

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

[danextian]

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.

[Floriankx]

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"