Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
(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.