Forum Discussion
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 .... Q3 1
I would like to have this result :
ID .... Q1 Q2 Q3
A1 .... 2 3 1
I need to do it in Power Query in order to be able to reproduce this transformation and use the same model for different CSV files but I am not able to do it because I can't use RATE in "Values Column", why ? How can i do it, please ?
Thank you !
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"
2 Replies
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"