Excel Connections vs. Queries: Step‑by‑Step Guide to Mastering Data Integration

What Are Connections and Queries in Excel?

Connections are the dynamic links used for building internal relationships in your workbook and external relationships between your and other workbooks.

Queries are tools used for importing data from different workbooks and then adding, deleting, or changing data as needed. The example below can help us better understand these terms.

We will use connections and queries to form a data table. We will merge two tables using the Excel Power Query feature. Table 1 contains information about student ID, student name, and math scores. The name of the first table is Math_Scores.

Table 2 will contain the marks for Physics and Chemistry. The name of the second table is Physics_Chem_scores. Here, the Name column is the common column between the two tables.

STEP 1: Create a Table in Excel

• Convert the dataset into table format.
• To do so, select any dataset cell and press Ctrl + T.
• The Create Table dialog box will appear.
• Click OK.

• The dataset is converted into a table.

• Convert the 2nd dataset into Table 2.

STEP 2: Load Data in the New Worksheet

• Select any cell from Table 1.
• Go to the Data tab and select From Table/Range.

• The Power Query Editor window will pop out.
• On the leftmost side of the window, you can see the Math_Scorestable.

• In the Power Query window, go to the Home tab and click on the Close & Load icon to open the drop-down menu.
• Select Close & Load To from there.

• The Import Data dialog box will appear.
• Select Only Create Connection and press OK to proceed.

• Repeat the above steps for the second table.
• You will see the tables in the Queries & Connections pan.
• Math_Scores is the first table, and Physics_Chem_scores is the second table.

STEP 3: Merge the Table in the Power Query Editor

• Go to the Data tab and select Get Data. A drop-down menu will appear.
• Select Combine Queries >> Merge from there. This will open the Merge window.

• In the Merge window, select the tables and matching columns to create a merged table.
• Select Math_Scores from the first drop-down menu.
• Select Physics_Chem_scores from the second drop-down menu.
• Select Left Outer (all from first, matching from second) in the Join Kind box.
• Select the Name column in both tables.
• Click OK to proceed.

• As a result, you can see a merged table in the Power Query Editor. But the Physics_Chem_scores column doesn’t contain any value.
• Click on the Expand icon to insert values in this column.

• A message box will appear.
• Unselect the Name column from there.
• Check ‘Use original column name as prefix’.
• Click OK to proceed.

• As a result, we can see the merged table in the window.

STEP 4: Insert the Merged Table in the Worksheet

• Go to the Home tab of the Power Query Editor window and click the Close & Load icon.
• Select Close & Load To from the drop-down menu.

• Select Table and New worksheet in the Import Data box.
• Tick on Add this data to the Data Model.
• Click Load to move forward.

• Finally, you will see the merged table in a new sheet named Merge 1.

Read More: Excel Queries and Connections Not Working

STEP 5: Show Connections vs Queries Differences

• After finishing, you can see the Queries & Connections pane.
• On the Queries tab, you can see 3 queries. The tables are termed Queries.

• In the Connections tab, you can see 1 connection: the workbook data model.

Download the Practice Workbook

To practice by yourself, download the following workbook.

Related Articles

• How to Create a Data Source in Excel
• How to Create Excel Data Connection to Another Excel File
• How to Refresh Data Connection in Excel Without Opening File
• [Fixed!] External Data Connections Have Been Disabled in Excel
• Data Connection Not Refreshing in Excel
• Excel VBA: How to Refresh All Data Connections

<< Go Back to Excel Data Connections | Importing Data in Excel | Learn Excel