Transpose multiple rows in to multiple columns in Directquery

[Anonymous]

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

[dufoq3]

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"

[lbendlin]

Consider doing that in the UI instead.

[Anonymous]

Hi @lbendlin Can you please explain How that can be acheived ?

[lbendlin · Accepted solution]

load the data as is, and put the question field in the columns area of your matrix visual.