Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Unpivot Error: Data Source Error

Hello,   inside the query editor I am trying to unpivot a table. Its a table from an Azure SQL Server and its in direct query mode.   However when trying to unpivot I get the following error:   ...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Based on my research, it seems to be an SQL query issue with the Unpivot operator. Followings are two similar thread for your reference.

https://stackoverflow.com/questions/11158017/column-conflicts-with-the-type-of-other-columns-in-the-unpivot-list

https://dba.stackexchange.com/questions/54353/why-does-sql-server-require-the-datatype-length-to-be-the-same-when-using-unpivo

 

In this scenario, I would suggest you try manually writing the Unpivot SQL query, and use it to import data from Azure SQL Database using Native Database Query with Power BI. :smileyhappy:

 

 

Regards

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hello v-ljerr-msft

 

thanks for the additional information and the suggested workaround. This would indeed work, however when using direct query AND a native database query its seems like you are quite limited in terms of data transformation within power query. For example even selecting columns to be deleted means that the mode has to be changed to import mode as those changes result in a query that is not supported in direct query mode.

 

I now changed the query to import mode to begin with (which I tried to avoid) but unpivoting works fine now.

 

Thanks, chris