Transpose multiple rows in to multiple columns in directquery

[ganes]

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

[Daniel29195]

Hello @ganes 

why not using the matrix visual  . this should give you the desired output. 

also if you want to do it in power query  follow the following steps : 
1.go to power query 

2.select the column questions 

3. click on pivot .  a new window will open

4.configure it as above.

you will have the desired output . 

hope this helps . 

[ganes]

Hi @Daniel29195,

Problem with Matrix visual is that I don't want to display all the questions. Also, I need to rename the questions in the Table view.

[ganes]

Hi @Daniel29195,

Thanks for responding when I am pivoting it is asking me to import. But I am using direct query as the table is huge more than 600GB

[Daniel29195 · Accepted solution]

@ganes 

oh wow, 

well yes,  directquery wont work with power query pivoting table , it will ask you to use import mode instead.

what about pivoting the table in the backend , with sql , or oracle or any language you are using in the backend 

or another approach maybe useful for you and easier in the backend. 

you can still use the matrix visual

and for the naming of the questions, you can create a new column callled, mapped questions name.

and for the requirement of that you dont want to show all questions, you can in the filter pane -->  filter on the visual, choose the questions that you want to show . 

does this help you solve your problem ?