Unpivot Error: Data Source Error

[Anonymous]

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:

DataSource.Error: Microsoft SQL: The type of column "Column5" conflicts with the type of other columns specified in the UNPIVOT list.

Unpivoting the same table seems to be working fine if its a different data source (Excel file import for example).

Any help is very much appreciated!

[MarkSL]

I came across this error today. 

My dataset would refresh without issue from within Power Bi Desktop, but failed in the service with "The type of column "xxx" conflicts with the type of other columns specified in the UNPIVOT list."

After finding this thread I checked my datatypes at source - SQL Server.  All columns being unpivotted were of type BIGINT.  When I changed this to INT (as my data allowed), the dataset refreshed without issue?!

Hope this may help someone in the future.

[supersharp]

Is there a no-code way to do this? The concept behind PowerQuery was to be able to do everything with clicks, however almost all the 'solutions' to problems involve some sort of code, python, and basically not suitable for business users?

[v-ljerr-msft · Accepted solution]

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. 

Regards

[Anonymous]

This post is over 2 years old but it just solved my problem. Thanks.

[Anonymous]

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