Collate in Power Query M?

[Anonymous]

I'm specifically trying to merge 2 tables but get a collation error. I want to resolve this error specifically in Power Query M. Not BI or SQL or anything.

Table 1 is my primary table with dozens of different columns - one of which is the Customer ID Number

Table 2 is dedicated to details of each customer ID number (Name, address, etc.)

I'm trying to merge the 2 on the Customer ID Number column, which works fine, but when I expand afterwards, I get the following collation error:

DataSource.Error: Microsoft SQL: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Details:
DataSourceKind=SQLDataSourcePath=REDACTED;Database Name
Message=Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Number=468
Class=16

I just want to know if it's possible to collate in M specifically.

[mahoneypat · Accepted solution]

Try adding an index column to one or both tables before the merge.  

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Monika_85]

hey Adding an index column worked for me. Thanks!

[Anonymous]

Very interesting, why does that work? Will the table I added the index column to still pull in new data upon refresh or does adding the Index column make it static?