Create Column headings from rows

[PhilDo]

Hey All, 

I have been pulling my hair out as I can not work out the best way to convert column values into row headings. I have the following table 'CW Results' with the following 

Name	QuestionNumber	Result	Test
Person A	1	A	1
Person A	2	B	1
Person A	1	B	2
Person A	2	C	2
Person B	1	A	1
Person B	2	C	1
Person B	1	B	2
Person B	2	B	2

 I am wanting to transpose this data so I have the following table showing up in the report

Name	1	2	Test
Person A	A	B	1
Person A	B	C	2
Person B	A	C	1
Person B	B	B	2

 The reason that I want to do this instead of a pivot column in "Transform Data" is that I have a table that has question number in the x-axis and result on the y-axis. I want this to interact with this table. I dont want to use a matrix as I want to choose the exact columns that I add to this table but as you can see, the question column in the 1st table is in row format and I want to great a dynamic table that has the questions and their results as per table 2.  What is the best way of creating this table?

Really appreciate your help 🙂 Cheers

[amitchandak]

@PhilDo , I have an unpivoted Question with the result. And the change code List.MAX

Full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lEyBGIIHauDImEExE7YJAyhEkbYdDijSzjhssMJSQeGBFY7nJBcBZSIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, QuestionNumber = _t, Result = _t, Test = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"QuestionNumber", Int64.Type}, {"Result", type text}, {"Test", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"QuestionNumber", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"QuestionNumber", type text}}, "en-US")[QuestionNumber]), "QuestionNumber", "Result", List.Max)
in
    #"Pivoted Column"

#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"QuestionNumber", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"QuestionNumber", type text}}, "en-US")[QuestionNumber]), "QuestionNumber", "Result", List.Max)

This was List.Count, I made it max

Find the file after Signature. See if this can help