Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Power Query - Pivot values to columns

Hi,   I have an ID with some other fields with 3 corresponding questions and a rate :    ID  ....   QUESTION   RATE A1  ....   Q1                 2 A1  ....   Q2                 3 A1  ....    ...
Need Help
Tutorial Requests
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous Please try to pivot the Question column and select "Don't Summarize". Below is the code that you can paste in the Advanced Editor in Power Query Editor.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEKBBFGSrE6ML4RkDBG4hsDCUOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Question = _t, Rate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Question", type text}, {"Rate", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Question]), "Question", "Rate")
in
    #"Pivoted Column"

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

Anonymous Please try to pivot the Question column and select "Don't Summarize". Below is the code that you can paste in the Advanced Editor in Power Query Editor.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEKBBFGSrE6ML4RkDBG4hsDCUOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Question = _t, Rate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Question", type text}, {"Rate", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Question]), "Question", "Rate")
in
    #"Pivoted Column"

Anonymous's avatar
Anonymous
Not applicable
7 years ago

It's working, thanks a lot !