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 Only"? 

I am worried there is a difference between "loading as a table" and Connection Only in regards to data refreshing.  

I noticed that in File A if I right clicked the loaded table on the sheet it didnt seem to refresh the data.  Or maybe I did something wrong or didnt wait long enough for the data to refresh.  I would think a "Connection" or "Load as a Table" shouldnt matter if running queries.   I am not sure if you have a connection or load as a table then in both cases you need to refresh the data in the source (File B in my case).  

 

thank you

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

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • Centaur's avatar
      Centaur
      Helper V
      4 years ago

      Hi Nate, understood.  One last question.  If the Connection is connected to the same file then I think it does refresh.  I mean that if the source data and the connection is in the same file I think a Connection refreshes if not mistaken. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Here's what I'd say about that. I say there definite is a difference, and that if you don't need to load as a table, then you shouldn't. A few things to think about:

     

    Suppose that QueryB references QueryA. The final step in QueryA has 5000 rows, and a column named "Events".

     

    QueryB references QueryA and it's 5000 rows and with its "Events" column. You then transform QueryB by removing the Events column, and by filtering some other stuff so your table had a few hundred rows.

     

    If you make QueryA Connection Only, lazy M is going to think of the most efficient, laziest way to evaluate QueryB. By not loading QueryA, QueryB is the only Query being evaluated for a result. M will basically make one efficient query, and will do the filtering and probably never even add the "Events" column, because it never needs it.

     

    But if you Load QueryA, M does have to grab and load the "Events" column, and all 5000 rows, and then QueryB has to evaluate QueryA in its fully evaluated instance, and then evaluate QueryB. 

    I think of Connection Only as "I still want my queries that reference QueryA to act like a continuation of QueryA." I think of Load as "I want my queries that reference QueryA to act like a brand new query that refers to the fully processed QueryA."

     

    I wish I could explain it more technically!

     

    --Nate

    • Centaur's avatar
      Centaur
      Helper V
      4 years ago

      Hi Nate, thank you very much.  I have a follow up. So if I load as a table as opposed to Connectuon and I right click refresh then it should give me the most recent deletions additions and whatever else to that table, correct? I just remember when I was testing it that it didn't seem like it was but I could've been doing something wrong. I just want to know from a more seasoned user that if I It is loaded as a table and right click refresh then it should give me the latest and greatest. I hope that makes sense. Thank you

  • 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"