# Power Query - only create connection option



## lezawang (Jun 15, 2021)

Hi
I watched some videos on how to merge 2 tables using Power Query (Vlookup alternative). All videos would use Data-->From Table/Range then once PowerQ editor open, they would "Close and Load To:" then select "Create Connection Only". I do not understand the Create Connection option, Why? They all said to create connection between the PowerQuery and that table. This what I do not understand, the Table already in Power Query editor and the connection is already established and brought that table to PowerQuery editor so why I need the connection?  Thank you very much.


----------



## Alex Blakenburg (Jun 15, 2021)

If quit out of the Power Query editor without saving, anything you have done in there is lost.
The way to save what you have done is through the close & load options.
If you have not got to your end result stage yet where you want to send data back to the workbook, then you only want to save the steps which includes the connection information. This is when you save only as a connection.


----------



## RoryA (Jun 16, 2021)

There are many reasons why you might not want your data returned as a table in a worksheet - it's too big; you're using it as a source for pivots; it's a query used in other queries; and so on. For all those cases, you'd use connection only.


----------



## lezawang (Jun 16, 2021)

Thank you all for the help. I appreciate it. Today I was thinking about it and came with this conclusion, not sure if I understand it correctly or not. Since I need to work on 2 tables to merge them, I can not put both of them in the PwerQ editor at same time, nor I want to "Load To" both of them to Excel since I need to work on/transform them first. So I "Get" the first table, but I need the second one? Before "Get" the second one, I move the first one in an area/buffer, lets called it "Connection." Now my first Query in the area called Connection (not in Power Q editor Nor in Excel)
Now I can go and "Get" the second table and do exactly the same, load it into the Buffer area (Connection area). Now both Queries/Tables in the buffer area (not PwerQ editor nor Excel Sheet). Now I open that area (buffer/Connection) by going to Data-->Get Data-->Combine Queries. In this window, I can decide how to combine them, and once I am done with that. I can now ask to put the merged table (1 table) into PowerQ editor.  This is my understanding, does that make sense?


----------



## Alex Blakenburg (Jun 17, 2021)

It sounds like you probably have it correct but the terminology is a bit confusing.
For both the tables you go through the process of 

Data > From Table/Range
Load & Close > Only Create Connection
Then if you start from Excel its Data >Get Data > Combine Queries > Merge
(but this is also available within PQ in various ways)

The terms buffer and not in PQ are where I am confused.
The connection only queries are in PQ and the PQ scripts / code are stored within the current workbook (just not in an obviously visible way).
You will see them in Excel in the Queries and Connections pane and you will also see them in the Queries pane in PQ.
If you click on the query in PQ you will see the output of that query just like any other PQ query, you have just chosen not to send that output back into an Excel worksheet because you don't need it there and its serving as an Input to another query.


----------



## lezawang (Jun 17, 2021)

Thank you Alex for the helop I appreciate it very much. Here is my next question if you do not mind please,  When a query is called query? If I have a table and then if I go to Data-->From Table and select that table, then Power Query would be open. If I "Close and Load" then the table will be loaded to the Excel sheet. That is called Query, right?

Now, if I do the same thing again but instead of Close and Load, I select "Close and Load To" then select "Create a connection only" then that would be called a Connection only.

So does that mean, this action

Data--> From Table/Range <<-- is not Query yet?

It will be called Query only if I "Close and Load" because if I "Close and Load To" then select "Create connection" Then that would be called "Connection"

Before, I was thinking the action to select a table and bring it to Power Query Editor is called Query. Can you please help to clarify this. Thank you very much.


----------



## Alex Blakenburg (Jun 18, 2021)

I would go with your last statement, that "the action to select a table and bring it to Power Query Editor is a query".
However if you quit out of Power Query and don't use one of the Close & Load options, the query script will be not be saved.

The Power Query scripts are saved within the Excel workbook when you Close & Load, so if you don't then save the workbook the script will be lost.
So the Only Create Connection option only saves the script inside the Excel workbook while the other options save the script but also return output back to a sheet in the workbook.


----------

