Power Query - only create connection option

[lezawang]

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]

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]

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]

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]

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]

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]

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.

 

[GB2]

How do I find the only create connection option on Mac?

 

[Alex Blakenburg]

I don't have access to a Mac version of Excel, I believe Rory does so he may chime in.
The latest I have from a year ago is that it wasn't available yet on a mac.

Bill Jelen en LinkedIn: Power Query for Mac is missing "Only Create Connection". In V2 of his…

Power Query for Mac is missing "Only Create Connection". In V2 of his add-in, Suat M. Ozgur added the missing Import Options dialog so you can Only Create…

ec.linkedin.com

 

[RoryA]

It's still not available. You should have a look at smozgur's MoreQuery add-in though, as it does enable that option: MoreQuery for Mac