Forum Discussion

Centaur's avatar
Centaur
Helper V
4 years ago
Solved

Connection Only or Load as Table and queries

Hello   I have a file:  File A This File A is a connection to File B. File B consolidates many files in a folder.    In File A, for developing queries, is "load as table" better or "Connection ...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    That's correct. Once you are all loaded and the editor is closed, it's only the Connection Queries that cannot be refreshed.

     

    --Nate

  • Anonymous's avatar
    Anonymous
    4 years ago

    It's does refresh, but only when you refresh the table that loads from the connection file, and only for what is needed for that file. If you have a connection file with no filters, and then three files that reference the connection file but then filter by region, when your files are loaded, and then you refresh only the West Region, the connection file (assuming query folding) query plan will filter to the West Region, and never bother with bringing in the other regions. Or if your West query only needs two columns, the connection query will select only those two columns.

     

    Try adding an unfiltered connection only file, then add some reference files that remove columns, filter dates, etc, and if they fold, look at the native SQL. You'll see what I mean. 

    --Nate

Centaur's avatar
Centaur
Helper V
4 years ago

HI Nate,

 

Sorry to pester but I have been doing a little testing.  I have verified that my file that is linked by not a data connection but rather loaded as a table does not refresh unless I open the source file and click refresh then save the file (I have to save the file or else it doesnt refresh the linked file)..  

 

I thought if had as a table and not a data connection then it woudl update without having to open the source file and refresh and save it. 

 

Maybe I am doing something wrong?  

thank you Nate. 

 

below are the query settings of the linked file that is loaded as a table 

 

here is the advanced editor:

let
Source = Excel.Workbook(File.Contents("C:\Users\AJ\Box\Financing\Project Finance\DRAWS\Liquidity Reserve\FBL1N Consolidated PQ.xlsx"), null, true),
#"FBL1N Pmts_Sheet" = Source{[Item="FBL1N Pmts",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"FBL1N Pmts_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Proposal Date", type date}, {"Company Code", type text}, {"Name", type text}, {"Amount in doc. curr.", type number}, {"Reference", type text}, {"Document Number", Int64.Type}, {"Pay Yes/No", type text}, {"Document Date", type date}, {"Posting Date", type date}, {"Net due date", type date}, {"Payment Method", type text}, {"Column16", type text}, {"Vendor", Int64.Type}, {"Payee/er", Int64.Type}, {"Clearing Document", type text}, {"Clearing date", type any}, {"Text", type text}, {"Part.bank type", type text}, {"Payment Block", type text}, {"Terms of Payment", type text}, {"Sheet1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [#"Amount in doc. curr."]*-1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Proposal Date", "Company Code", "Name", "Amount in doc. curr.", "Custom", "Reference", "Document Number", "Pay Yes/No", "Document Date", "Posting Date", "Net due date", "Payment Method", "Column16", "Vendor", "Payee/er", "Clearing Document", "Clearing date", "Text", "Part.bank type", "Payment Block", "Terms of Payment", "Sheet1"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Amount in doc. curr."}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Amount in doc. curr."}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Amount in doc. curr.", Currency.Type}})
in
#"Changed Type1"