Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

 

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!

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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 BISmiley Happy

 

gd1.PNG

 

Regards

View solution in original post

5 REPLIES 5
MarkSL
Helper V
Helper V

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
Regular Visitor

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
Employee
Employee

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 BISmiley Happy

 

gd1.PNG

 

Regards

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.