Error in refreshing dataflow - cannot resolve conflict

[oBi1]

Hello!

I'm hoping someone can shed some light on this and help me resolve it.

Environment: PBI service, PPU workspace

Dataflows (simplified):
1) load excel file from sharepoint

2) some transformations using the dataflow 1 as a 'linked table'

3) final dataflow using transformed table from 2 as a 'linked table'

Error comes when trying to refresh 1) (or 2). Apparently the error appears in the last step (as tables are linked refreshing one immediately refreshes the rest of the chain).:

"Error: Data Source Error : DataSource.Error: Microsoft SQL: Cannot resolve collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and "Latin1_General_100_BIN2_UTF8" in add operator for DISTINCT operation.. RootActivityId = c3fa9030-a484-4e6d-8878-e44fbed51080.Param1 = DataSource.Error: Microsoft SQL: Cannot resolve collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and "Latin1_General_100_BIN2_UTF8" in add operator for DISTINCT operation. Request ID: cb66b944-c606-93bc-4c20-71db297e7a34."

The m code for dataflow 3:

let

    Source = #"TRF_Allowance Data per EE (unpivoted)",

    #"Choose columns" = Table.SelectColumns(Source, {"Worker ID", "Worker", "Allowance Plan", "Allowance Amount Annual", "Allowance Amount Annual (USD)"}),

    #"Renamed columns 1" = Table.RenameColumns(#"Choose columns", {{"Allowance Amount Annual", "[Annualized]"}, {"Allowance Amount Annual (USD)", "[Annualized, USD]"}}),

    #"Sorted rows 1" = Table.Sort(#"Renamed columns 1", {{"Allowance Plan", Order.Ascending}}),

    #"Unpivoted columns 1" = Table.UnpivotOtherColumns(#"Sorted rows 1", {"Worker ID", "Worker", "Allowance Plan"}, "Attribute", "Value"),

    #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Unpivoted columns 1", "Plan", each [Allowance Plan] & " - " & [Attribute]), {{"Plan", type text}}),

    #"Removed columns 2" = Table.RemoveColumns(#"Added custom", {"Allowance Plan", "Attribute"}),

    #"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Removed columns 2", {{"Plan", type text}}), List.Distinct(Table.TransformColumnTypes(#"Removed columns 2", {{"Plan", type text}})[Plan]), "Plan", "Value"),

    #"Sorted rows" = Table.Sort(#"Pivoted column", {{"Worker ID", Order.Ascending}}),

    #"Error Check" = let

    #"Grouped rows 1" = Table.Group(#"Sorted rows", {"Worker ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

    #"Error Count" = Table.RowCount(Table.SelectRows(#"Grouped rows 1", each [Count] > 1))

in

    if #"Error Count" > 0

    then

    error

    Error.Record("Duplicate error", "Worker ID field is not unique!")

    else #"Sorted rows"

in

    #"Error Check"

I've already gotten to the point that it appears the pivot step is the culprit. But I'm not completely sure and I lack the skills to further resolve this. 

Thanks a lot for any thoughts and guidance

[oBi1 · Accepted solution]

Update for the community:

It ended up having to do with some kind of implicit data type casting before the unpivot step. The data tupes coming in from the linked table look perfrectly fine. But then the unpivot step fails. I simply tried to do a 'detect data type' step before the unpivot!

Solved. Not sure what's going on but linked tables (from other data flows) somehow can give some challenges here and there.

[lbendlin]

Since this is a SQL error it means that at least some of your Power Query steps are folding.  Right click each step, click "View Native Query"  and then run that SQL in the data source. That will tell you why the collation conflict happens.

Out of curiosity - why would you have such very different collations in your SQL Server?

[oBi1]

Hi lbendlin - it doesn't work like that for me unfortunately. When I right click the steps I don't see that option: there is 'View data source query' and 'View query plan'. The first one is greyed out in every step. The second seems to provide just a visual representation of the query steps.

Edit: to your question on running the sql query? if I could find it, I also wouldn't know how to do that as I think it is something PBI/power query does in the background with the dataflows. Maybe that to some extent uses a sql instance, no idea... I wouldn't know how to run that though...

And I don't understand the question about collation. I just need those steps to get to my final output.

Please note I'm working online with data flows.

As I'm using linked tables guess that's what's causing some query folding (compute engine is set to the standard setting).

[lbendlin]

You should be able to take the Power Query code from the dataflow and duplicate it in the Power Query editor of the Power BI Desktop.  Makes troubleshooting easier.