Pivot Live Direct Query Data

[Anonymous]

I appologize if I am not using the correct terms so please bear with me.

I am using a direct query link to a SQL server of a pull of data that stores the data in vertical table instead of a horizonal field.  Meaning instead of having one row for an entry and several columns for each 'question' it saves each question for an entry as a different row so one entry could be 10 rows.  Is there a way to change this, pivot it, to make the entries show as one row and the columns are the 'questions'?  Keep in mind this is a direct query from the source.

Also going to the source to do this before I get the data is not an option as of yet, that is why I am here.

Thank you for any and all help.

[v-huizhn-msft]

Hi @Anonymous,

You can use SQL functions PIVOT and UNPIVOT in SSMS and debug it to get expected result. When you type it in box highlighted in red line. You will load desired table.



Best Regards,
Angelia

[Anonymous]

I will give this a try but not familiar with SQL code.  I was hoping to be able to do it in Power BI but will look into this route.  Thanks

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

You use direct Query mode, the data is not imported into Power BI, and there are many limitations about direct mode. You'd better use SQL Code to get expected data as above said. Could you please mark reply as answer if you think it's helpful.

Thanks,
Angelia

[Anonymous]

I am still checking into this and waiting to see if there might be other ways to get this done, but am still looking into the SQL PIVOT.  Thanks