Dataflow refresh failing on Merged table (Cannot resolve the collation conflict)

[WNCPBI]

I am importing an Excel workbook into a PPU Workspace Dataflow.

From the table, I've created a star schema with four dimension tables and a fact table. Three of the Dimension tables are creating and merging into the fact table created from a reference to the main table. However, the final table is throwing an error. To do the final merge I'm unpivoting some columns (in the new dimension and fact tables (they are all the same data type)) and then adding an index column and doing the merge. This is apparently working in Power Query, but failing on the Dataflow refresh with the following error:

Error: Data Source Error : DataSource.Error: Microsoft SQL: Cannot resolve the collation conflict between "Latin1_General_100_BIN2_UTF8" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation. DataSourceKind = <ccon>SQL</ccon> DataSourcePath = <ccon>.\cdsasqlcache;db$0056c321-7c84-4a82-b68b-cef9f38db461</ccon> Message = <ccon>Cannot resolve the collation conflict between "Latin1_General_100_BIN2_UTF8" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.</ccon> ErrorCode = <ccon>-2146232060</ccon> Number = <ccon>468</ccon> Class = <ccon>16</ccon> State = <ccon>9</ccon>. RootActivityId = 96a2329a-98b1-464e-9a38-52bec4fe1476.Param1 = DataSource.Error: Microsoft SQL: Cannot resolve the collation conflict between "Latin1_General_100_BIN2_UTF8" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation. DataSourceKind = <ccon>SQL</ccon> DataSourcePath = <ccon>.\cdsasqlcache;db$0056c321-7c84-4a82-b68b-cef9f38db461</ccon> Message = <ccon>Cannot resolve the collation conflict between "Latin1_General_100_BIN2_UTF8" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.</ccon> ErrorCode = <ccon>-2146232060</ccon> Number = <ccon>468</ccon> Class = <ccon>16</ccon> State = <ccon>9</ccon> Request ID: 2e122e42-f9b5-e09c-98ba-788c95af8814. 

Any ideas?

[watkinnc]

There is an option labeled "Using Locale" when you right click on a column header in Power Query. I would go through each of the columns in your tables and choose that option to update your data types. You have two different "locales" somewhere.

--Nate

[WNCPBI · Accepted solution]

Thanks for the reply. I thought that might be the issue too, but setting the locale on the affected columns didn't work either.  I have found that setting the "Enhanced Compute Engine" to Disabled for the dataflow does resolve the issue. Whether this will have any unintended consequences through, I'm not sure, but for now it resolves the issue.