CODEWITHSUNDEEP
excelpowerbipowerquery
David542
David542

Reputation: 110093

Connection vs Query in Excel data model

What is the difference between a 'connection' and a 'query' as Excel defines it in their data model? For example, if I load a csv file from local, it stores that as a query (it shows a 'select *' as the query against the file). What then would be considered a connection and what's the difference between the two? The only thing I can think of is that a connection would not return data without specifying the table/query to use-- for example, a database connection where it has multiple tables, and possibly, connecting to another Excel file if it had more than one tab.

Reference: https://support.microsoft.com/en-us/office/create-edit-and-manage-connections-to-external-data-89d44137-f18d-49cf-953d-d22a2eea2d46

Upvotes: 3

Views: 24836

Answers (2)

teylyn
teylyn

Reputation: 35900

Every query is also a connection, but not all connections are queries.

Connections have been in Excel for a long time, typically associated with commands that access data source like SQL server etc. The page you linked to has more links at the bottom. You may want to read up there.

The term "query" is now typically associated with Power Query, where the data connection to the data source is made via the Power Query engine, and then further refined in the query editor.

So, each query (Power Query) also has a connection, but you can only edit Power Queries in the Power Query editor, whereas legacy connections can be edited in the properties dialog of the connection.

Edit: Let's put it this way: The connection is just that. It connects your workbook to a data source. Like a highway connecting two cities.

A query is the request for actual data that you spell out, calling from your workbook (via the connection) into the data source. The data source then sends the data back (via the connection). The mechanics of asking for, receiving, and manipulating the received data (for e.g. cleaning it up, storing it in the workbook) is what the query does, but it can't do this without the connection. The query is the actual traffic on the highway.

Before Power Query, you could also connect to SQL Server and return data. The query details are visible in a tab in the connections dialog, so connection and query were used synonymously. These legacy data tools are now hidden by default and must be activated in the Excel Advanced options.

With Power Query, the brand name influences the use of the terminology. The term "query", more often than not now means Power Query, whereas some people may use "connection" (which is always a part of any query) for old style, legacy data connections (which also contain queries).

However, when you use Power Query, each of these queries will use connections. These are established when you first create the query. Your workbook may have a number of connections to different data sources. The credentials for each data source are stored with the connections (on your computer), not in the Power Query. This is like your toll fee for the highway. By storing the credentials with the connection, you establish the permission to use the connection and it doesn't matter how many people you bring back in your bus.

You can even use the same connection (to a specific SQL Server) for several different queries. When you create the first query to the SQL Server, you are prompted for credentials for that new connection (your toll for the highway). When you create another query to the same SQL Server, the connection already exists and you are not prompted for your credentials.

You can drive your bus along that same highway several times and pick up people from different suburbs of the city that the highway connects you to.

Your highway toll fee is only valid for a limited time. You can take as many trips as you want, but it will expire after some time. (This happens with SharePoint credential after 90 days, after which you have to provide your credentials again. Don't know about SQL Server, though.)

When you send a workbook with a query to SQL Server to someone else, they need to provide their own credentials in order to use the connection. Your toll fee does not cover their bus.

I'm going to stop now before this turns into a children's book.

Hope it helps.

Upvotes: 10

BirdMan
BirdMan

Reputation: 171

In addition, Connections is a dynamic link and can be set to enable on:

  • background refresh
  • when the file opened
  • refresh every X minutes or
  • Refresh when the queries are refreshed.

However, Query is a more static link and needs to be refreshed manually to load the latest data.

Upvotes: 0

Related Questions