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:

 

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!

Need Help

5 Replies

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • supersharp's avatar
    supersharp
    Regular Visitor
    5 years ago

    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?

  • MarkSL's avatar
    MarkSL
    Helper V
    3 years ago

    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.