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.
1st 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
2nd 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.
3rd 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
4th 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.
5th 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.
6th Step: Make Necessary Selections in the Merge Window (Same as the 3rd 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
<< Go Back to Excel Union | Excel Operators | Excel Formulas | Learn Excel