Forum Discussion
Transpose multiple rows in to multiple columns in Directquery
Hi All,
can anyone please help me to transpose question colum into rows. And it should have its correseponding answer. I am pulling data from bigquery.
column 1 (Number) has 5 entries for 5 diff questions and corresponding answers. I need the 5 questions in indivisual column and its answers get filled in as per the number
load the data as is, and put the question field in the columns area of your matrix visual.
4 Replies
Consider doing that in the UI instead.
You can achieve this result
using UI:
or create Blank Query and paste there this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdG9aoJBFIThe/lqC8/M+Fd6CUkrYmVhYyAmePtJILCLHGa6Xd5TzXM6Le+1rJa37+vj6/Zxv/x9jvfH8/r5+zyvXjNGRpM5MpuskdXkzcibJm9H3jZ5N/KuyfuR900+jHxocq2nXdbdwTxct1xN01W3XU3jVbdeTfNVt19NA1a3YE0T1v+G8Prw+vD68Prw+vD68Prw+vD6SPpI+kj6SPpI+kj6SPr0+vT69Pr0+vT69Pr0+vT69PpM+kz6TPpM+kz6TPpM+vL68vry+vL68vry+vL68vry+kr6SvpK+kr6SvpK+nL65x8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Number = _t, Question = _t, Answer = _t]), #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Question]), "Question", "Answer") in #"Pivoted Column"
