How to Perform Union Query in Excel (with Detailed Steps)

When working with Excel, sometimes we get multiple tables with one common column or row. As a result, it is needed sometimes to merge the tables for better data representation. Now, this can be achieved through the Union query in Excel. In this article, I will show you all steps to perform a Union query in Excel.

How to Perform Union Query in Excel: Step by Step

Say, you have 3 individual datasets Sales Data, Product ID, and Region List. Now, these datasets are related to each other. So, you want to perform a union query between them to merge them into one table. Follow the steps below to achieve this.

1^st Step: Collect Data and Create New Query Connection

To perform a union query, you must prepare the datasets into tables and create connections.

• To do this, first, click on any cell inside your required dataset.
• Following, go to the Data tab >> From Table/Range tool.

• As a result, the Create Table window will appear. Here, click on the OK button.

• Afterward, the Power Query Editor window will open automatically.
• Following, at the Query Settings pane, under the Properties group, write your table’s name (SalesData here) in the Name text box.

• Afterward, under the File tab, click on the Close & Load arrow and select the option Close & Load To…

• As a result, the Import Data window will appear.
• Following, choose the Only Create Connection option and click on the OK button.

• As a result, the SalesData table will be prepared to merge.
• Now, follow similar procedures for all the other datasets to prepare them to perform a union query.

Finally, you can see this on the right side of your Excel file in the Queries & Connections window.

Read More: How to Create Union of Two Tables in Excel

2^nd Step: Proceed Next to Combine the Queries

Now, you can perform a union query between these tables.

• First and foremost, go to the Data tab >> Get Data tool >> Combine Queries option >> Merge option.

3^rd Step: Make Necessary Selections in the Merge Window

• As a result, the Merge window will appear.
• Now, we want to perform a union query between the SalesData table and the ProductID table first.
• To do this, at the first Preview window, choose the SalesData table, and at the second Preview window, choose the ProductID table.
• Afterward, click on the common column (Product column here) from both tables.
• Following, choose the Join Kind as Left Outer option and click on the OK button.

Read More: How to Do Union of Two Columns in Excel

4^th Step: Place the Values Properly

• As a result, the merge will take place, but the values haven’t been placed properly.
• Now, click on the double arrow button inside the ProductID header.

• As a result, a dropdown will appear.
• Subsequently, tick on the Product ID option only >> untick the Use original column name as prefix >> click on the OK button.

• Finally, you will see the two tables are merged properly now.

5^th Step: Proceed to Create Another Connection

• Now, as you need to merge another table, go to the File tab here >> Close & Load arrow >> Close & Load To… option.

• Consequently, the Import Data window will appear.
• Now, click on the Only Create Connection option and click on the OK button.

6^th Step: Make Necessary Selections in the Merge Window (Same as the 3^rd Step)

• As a result, you will be able to import the merged table as the Merge1 table.
• Now, repeat the merging procedures above to merge this Merge1 table and the RegionList table.
• So, at the first Preview window, choose the Merge1 table, and at the second Preview window, choose the RegionList table.
• Following, click on the Country column on both tables >> choose the Left Outer as Join Kind option and click on the OK button.

• As a result, the RegionList column will also be merged into the previously merged table.
• Now, for proper values, click on the double arrow button on the RegionList header.

• Consequently, a dropdown will appear.
• Following, tick on the Region option only and untick the option Use original column name as prefix.
• Subsequently, click on the OK button.

• As a result, you will get the proper values in the Region column.

Read More: How to Make Union of Two Sheets in Excel

Last Step: Close and Load Data

• Now, under the File tab, click on the Close & Load button.

Thus, you will be able to merge all three tables with a union query in Excel.

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

So, in this article, I have shown you all the steps to perform a union query in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are welcome to comment here if you have any further questions or recommendations.

Related Article

• How to Use VBA Union Function in Excel

<< Go Back to Excel Union | Excel Operators | Excel Formulas | Learn Excel