Issue / bug: Power Query M cross-database join fails - database connection info is switched

[Anonymous]

(Trying to post in "issues" forum, didn't see how to.)

My goal:

1) get data from server1/db1/table1 and from server2/db2/table2

2) union those 2 tables together

3) join the result with server2/db2/table3

I get a DataSource.Error Invalid object name error that looks like a bug to me, saying it couldn't find table1 on server2 - mixed up!

Am I doing something wrong, or is this a bug/failure in Power Query?

Relevant code:

let
    // set date in 1 place
    BackstopDate = #datetime(2018, 1, 1, 0, 0, 0),


    // Load & shape individual tables
    Table1_Source = #"SQL/Server1;Database1",
    Table1 = Table1_Source{[Schema="dbo",Item="Table1"]}[Data],
    Table1_1 = Table.SelectRows(Table1, each [As_Of_Dt] >= BackstopDate),
    //...
    Table1_Final = [DATA TRANSFORMATIONS GO HERE],

    
    Table2_Source = #"SQL/Server2;Database2",
    Table2 = Table2_Source{[Schema="dbo",Item="Table2"]}[Data],
    Table2_1 = Table.SelectRows(Table2, each [As_Of_Dt] >= BackstopDate),
    // ...
    Table2_Final = [DATA TRANSFORMATIONS GO HERE],
    

    Table3_Source = #"SQL/Server2;Database2",
    Table3 = Table3_Source{[Schema="dbo",Item="Table3"]}[Data],
    Table3_1 = Table.SelectRows(Table3, each [As_Of_Dt] >= BackstopDate),
    // ...
    Table3_Final = [DATA TRANSFORMATIONS GO HERE],
    

    // Union Table1, Table2 together - this works fine
    Table12Union = Table.Combine({Table1_Final, Table2_Final}),
    Table12Union_Final = [DATA TRANSFORMATIONS GO HERE],


    // Join Table3 to Table12Union_Final - this fails with crossed-up connection string info
    Table3_Table12Union = Table.Join(Table3_Final, {keycols}, Table12Union_Final, {keycols}, JoinKind.LeftOuter),


    // Set output
    Output = Table3_Table12Union


    in    
    Output
    
    
    
DataSource.Error: Microsoft SQL: Invalid object name 'Table1'.
Details:
    DataSourceKind=SQL
    DataSourcePath=Server2;Database2
    Message=Invalid object name 'dbo.Table1'.
    Number=208
    Class=16

[v-qiuyu-msft]

Hi @Anonymous,

Please modify this query part then test again: 

// Join Table3 to Table12Union_Final - this fails with crossed-up connection string info
    Table3_Table12Union = Table.NestedJoin(Table3_Final, {"keycols"}, Table12Union_Final, {"keycols"}, "Table12Union_Final", JoinKind.LeftOuter),

Instead of creating append and merge these three tables in one query, please create three queries to get data from these three tables then perform append and merge steps to see if the same issue occurs. 

Please set privacy level for these two data sources as below: 

By the way, please run the Power BI desktop latest version 2.61.5192.601 64-bit (August 2018). 

Best Regards,
Qiuyun Yu 

[Anonymous]

Thanks for the reply. Pretty sure I tried that, but I'll give it a go just to be sure!

[Anonymous]

So I:

1) switched from SSAS Tabular to Power BI (latest version)

2) Realized the cross-database issue can be demonstrated with just the append (no need for join)

3) Factored the data load into 3 separate queries:

    a) server1/db1/table1

    b) server2/db2/table2

    c) FinalResult = Table.Combine({table1, table2})

4) Set privacy to organizational as you suggested.

In the query editor window, the final append result displays fine. When I close & apply to load into power bi model, I get the same error.

Seems like union-ing 2 tables from different servers/databases shouldn't be this hard lol - what am I missing?