Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
(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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.