Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bpearce
Advocate I
Advocate I

gateway refresh error "references other queries... please rebuild" - Merge 2 tables from SQL Server

I see several posts related to this error so I offer my workaround. Goal is to merge two SQL Server tables in Power Query. Note that a "join" of the tables in SQL Server is not an option in my environment so the merge must occur in Power Query. If the data sets were small this would be no problem. I would stay with the GUI and define a query for each table and then merge. This can be published and refreshed via the Data Gateway. 

 

In my case however I need to pull roughly 20K records from the 30 million record Inventory table based on a list of Branches. Would be painful to pull all those records only to "merge out" all but the 20K records desired. So I opted to dynamically build the SQL query. Trick is to build a text string list of Branches in the format of a SQL "where... in..." clause. My habit is to derive the list of Branches with a Power Query via the GUI:

let
    Source = Sql.Database("srv2", "SCS_BI"),
    BranchDim = Source{[Schema="dbo",Item="BLT$"]}[Data],
    Filtered_Area = Table.SelectRows(BranchDim, each [Area] = "SCS" and [SlsOfc] <> null and [SlsOfc]<>""),
    RenamedColumns = Table.RenameColumns(Filtered_Area,{{"SlsOfc", "Branch"}}),
    RemovedOtherColumns = Table.SelectColumns(RenamedColumns,{"Branch"})
in
    RemovedOtherColumns

Then I create a query via the GUI to pull the Inventory records, but then drop to Advanced Editor to add the top lines:

let
//
vBranchList = Table.Column(BranchList,"Branch"),
vBranchListSQL = "'" & List.Accumulate(vBranchList, "", (state, current) => if state = "" then current else state & "','" & current) & "'",
vSQL = "select * from db1.sbi.Inventory where Branch in (" & vBranchListSQL & ")",
//
    Source = Sql.Database("srv1", "db1",[Query=vSQL]),
    RemovedOtherColumns = Table.SelectColumns(Source,{"Branch", "PartNo", "Stock Qty", "Stock Value"})
in
    RemovedOtherColumns

I can't begin to explain the List.Accumulate(), I just know it produces a text string of the form:

'BranchEast','BranchWest','BranchAnother' etc

This text string is then used to build the actual SQL query stored in vSQL which is passed to the Sql.Database() function. The net result is a very efficient SQL query that pulls only the 20K records I am seeking.

 

This is all well and good exept the Data Gateway doesn't like it. I can refresh this Power Query in PBI Desktop all day, but the Data Gateway throws the error "referenses other queries... rebuild...". ARG!!!

 

The workaround? Combined the two queries into a single query. Its not fun and seems a waste of time... but for now this how I get it to work. Here is the combined query:

 

let
//
    Source1 = Sql.Database("srv2", "SCS_BI"),
    BranchDim = Source1{[Schema="sbi",Item="BranchDim"]}[Data],
    Filtered_Area = Table.SelectRows(BranchDim, each [Area] = "SCS"),
    Filtered_BranchNull = Table.SelectRows(Filtered_Area, each [Branch] <> "" and [Branch] <> null),

//
vBranchList = Table.Column(Filtered_BranchNull,"Branch"),
vBranchListSQL = "'" & List.Accumulate(vBranchList, "", (state, current) => if state = "" then current else state & "','" & current) & "'",
vSQL = "select * from odin.sbi.Inventory_Daily where plant in (" & vBranchListSQL & ")",
//
    Source = Sql.Database("srv1", "db1",[Query=vSQL]),
    RemovedOtherColumns" = Table.SelectColumns(Source,{"Branch", "PartNo", "Stock Qty", "Stock Value"})
in
    RemovedOtherColumns

 

1 REPLY 1
v-yuezhe-msft
Employee
Employee

@bpearce,

Thanks for your sharing.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors