Excel Connections vs Queries: Main Dissimilarity with Example

In this article, we will show the main dissimilarity between connections vs. queries in Excel. Data Table is a very useful tool in Excel and is mainly used for forming data from different tables with connections and queries. Therefore, it is necessary to know the difference between them. So, without the delay, let’s start the discussion.

What Are Connections and Queries in Excel?

Connections are the dynamic links that are 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 per requirements. We can gain a better understanding of these terms by following the example below.

Demonstrate Dissimilarity Between Connections and Queries in Excel: Step-by-Step Procedures

For forming a data table, we will use connections and queries. We will merge two tables using the Excel Power Query feature. In Table 1, there will be 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.

Let’s pay attention to the step-by-step procedures to understand the dissimilarity between connections and queries in Excel.

STEP 1: Create Table in Excel

• Firstly, we need to convert the dataset into table format.
• To do so, select any cell of the dataset and press Ctrl + T.
• As a result, the Create Table dialog box will appear.
• Click OK.

• So, the dataset is converted into a table.

• Similarly, convert the 2nd dataset into Table 2.

Read More: How to Find External Data Connections in Excel

STEP 2: Load Data in New Worksheet

• In the following step, select any cell from Table 1.
• After that, go to the Data tab and select From Table/Range.

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

• After that, 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.

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

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

Read More: How to Close Queries and Connections Pane in Excel

STEP 3: Merge Table in Power Query Editor

• In the following step, navigate to the Data tab and select Get Data. A drop-down menu will appear.
• After that, select Combine Queries >> Merge from there. This will open the Merge window.

• In the Merge window, we have to select the tables and matching columns to create a merged table.
• Therefore, select Math_Scores from the first drop-down menu.
• Then, select Physics_Chem_scores from the second drop-down menu.
• Also, select Left Outer (all from first, matching from second) in the Join Kind box.
• Now, most importantly 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.
• So, click on the Expand icon to insert values in this column.

• A message box will appear.
• Now, unselect the Name column from there.
• Also, 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 Merged Table in Worksheet

• In the following step, go to the Home tab of the Power Query Editor window and click on the Close & Load icon.
• After that, select Close & Load To from the drop-down menu.

• In the following step, select Table and New worksheet in the Import Data box.
• Then, give a tick on Add this data to the Data Model.
• After that, 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 Dissimilarity

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

• And in the Connections tab, you can see 1 connection, and that is the workbook data model.

Download Practice Workbook

To practice by yourself, download the following workbook.

Conclusion

In this article, we have demonstrated the way to differentiate between Excel connections vs queries. There is a practice workbook at the beginning of the article. Go ahead and give it a try.  Last but not least, please use the comment section below to post any questions or make any suggestions you might have.