Forum Discussion

PhilDo's avatar
PhilDo
New Member
5 years ago

Create Column headings from rows

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 

 

NameQuestionNumberResultTest
Person A1A1
Person A2B1
Person A1B2
Person A2C2
Person B1A1
Person B2C1
Person B1B2
Person B2B2

 

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

 

Name12Test
Person AAB1
Person ABC2
Person BAC1
Person BBB2

 

 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

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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