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.

Download Practice Workbook

You can download our practice workbook from here for free!

Steps to Perform Union Query in Excel

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 (6 Simple Methods)

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 (5 Easy Ways)

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 (4 Suitable Methods)

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.

Conclusion

So, in this article, I have shown you all 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.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!

Related Articles

• VBA Union Method to Join Range in Excel (3 Examples)
• How to Use VBA Union Function in Excel (3 Easy Examples)