Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Collation Error

I have a Power BI project and I'm using DirectQuery. I'm trying to add a table visual. When I drag the value I want to the table field, it gives me the following error:   Cannot resolve the collati...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Thank you, but I already found a solution without having to change the collation of the table from SQL Server. I did the following:

     

    Create a View of the table you need from SQL Server, but do it like so:

     

    USE [enter_database_name_here]
GO

CREATE VIEW [dbo].[enter_view_name_here]
as
Select [column_name] collate SQL_Latin1_General_CP1_CI_AS as new_column_name

from table_name

    Basically just collate all the columns. Now you can import or DirectQuery this view and you'll get the data.

     

    I hope this helps anyone looking for an answer to this problem.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous,


Firstly, please check the collation of the table and columns that you imported into Power BI Desktop using T-SQL. If there is any difference between the collation, the above error would occur.

SELECT DATABASEPROPERTYEX('Yourdatabase', 'Collation')
GO
/* Find Collation of SQL Server Database Table Column */
USE yourdatabase
GO
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U')


Secondly, in SQL Server, change collation for problem columns with the T-SQL below.

ALTER TABLE dbo.yourtable ALTER COLUMN yourcolumn
            datatype COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;  
GO


There is a similar thread for your reference.
https://community.powerbi.com/t5/Integrations-with-Files-and/Couldn-t-load-the-data-for-this-visual-Exception-raised-by-the/td-p/98722

Regards,
Lydia