Join Multiple Excel Tables with Power Query
![Join Multiple Excel Tables with Power Query 1 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.1_Join-Multiple-Excel-Tables-with-Power-Query-1024x393.png)
Introduction
![Join Multiple Excel Tables with Power Query 2 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.2_Join-Multiple-Excel-Tables-with-Power-Query.png)
The second sheet has got other information of the employee such as Job Title, Department, City, State, Postal Code and Country.
![Join Multiple Excel Tables with Power Query 3 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.3_Join-Multiple-Excel-Tables-with-Power-Query-1024x407.png)
Note: The ID column which you can also consider as Employee ID is common in both the sheets.
What we want to achieve?
We want to create a new view in Excel which has all the information of the employees from both the sheets.
Steps to join two data sets using Power Query:
Let’s start from a new Excel file
![Join Multiple Excel Tables with Power Query 4 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.4_Join-Multiple-Excel-Tables-with-Power-Query.png)
From the Data ribbon, click on Get Data button
![Join Multiple Excel Tables with Power Query 5 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.5_Join-Multiple-Excel-Tables-with-Power-Query.png)
As our data is in Excel file; hence we will select “From Workbook” option under “From File”
![Join Multiple Excel Tables with Power Query 6 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.6_Join-Multiple-Excel-Tables-with-Power-Query.png)
Select the file which contain the employee data and click on ‘Import’ button
![Join Multiple Excel Tables with Power Query 7 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.7_Join-Multiple-Excel-Tables-with-Power-Query.png)
As our data is spread across two different sheets so we need to check the “Select multiple items” checkbox on the query window
![Join Multiple Excel Tables with Power Query 8 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.8_Join-Multiple-Excel-Tables-with-Power-Query.png)
Now let’s select both the sheets and click ”Transform Data” button
![Join Multiple Excel Tables with Power Query 9 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.9_Join-Multiple-Excel-Tables-with-Power-Query.png)
In few moments, Power Query Editor should get loaded
![Join Multiple Excel Tables with Power Query 10 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.10_Join-Multiple-Excel-Tables-with-Power-Query-1024x542.png)
The next task is to merge both the queries and create a combined query. To do this, we need to select “Merger Queries and New” under Home ribbon.
![Join Multiple Excel Tables with Power Query 11 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.11_Join-Multiple-Excel-Tables-with-Power-Query-1024x481.png)
In the merge dialog box, we need to select the tables which we want to merge. Here we have selected “Emp Data 1” and “Emp Data 2”
![Join Multiple Excel Tables with Power Query 12 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.12_Join-Multiple-Excel-Tables-with-Power-Query.png)
Next, we need to select the column which will be used to join both the tables together. You should select a column which is available in both the tables. In our case it is ID column which is available in both tables.
![Join Multiple Excel Tables with Power Query 13 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.13_Join-Multiple-Excel-Tables-with-Power-Query.png)
The last step to merge the table is to select the join type. Here we will be selecting Left Outer join which means all the records from “Emp Data 1” and only the matching records from “Emp Data 2” will be included in the result.
![Join Multiple Excel Tables with Power Query 14 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.14_Join-Multiple-Excel-Tables-with-Power-Query.png)
The query is created, let’s rename it to “Emp Full Data”
![Join Multiple Excel Tables with Power Query 15 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.15_Join-Multiple-Excel-Tables-with-Power-Query.png)
We need to expend “Emp Data 2” field to get the columns from second table (in our case the columns from “Emp Data 2” sheet. Let’s click on expend button
![Join Multiple Excel Tables with Power Query 16 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.16_Join-Multiple-Excel-Tables-with-Power-Query.png)
Let’s select all the columns apart from ID (as ID column is already available in Emp Data 1) and click on OK button
![Join Multiple Excel Tables with Power Query 17 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.17_Join-Multiple-Excel-Tables-with-Power-Query.png)
You will notice that the selected columns are added in the Query
![Join Multiple Excel Tables with Power Query 18 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.18_Join-Multiple-Excel-Tables-with-Power-Query-1024x407.png)
Now we need to remove the table name (Emp Data 2) from the newly added column names
![Join Multiple Excel Tables with Power Query 19 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.19_Join-Multiple-Excel-Tables-with-Power-Query.png)
Once done, we will click on “Close & Load” button under Home ribbon
![Join Multiple Excel Tables with Power Query 20 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.20_Join-Multiple-Excel-Tables-with-Power-Query.png)
Our query is ready. You will notice that 3 new sheets have been added in the file
![Join Multiple Excel Tables with Power Query 21 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.21_Join-Multiple-Excel-Tables-with-Power-Query-1024x395.png)
In our case “Sheet4” contains the view that we were looking for. Let’s rename it to “Full Data”
![Join Multiple Excel Tables with Power Query 22 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.22_Join-Multiple-Excel-Tables-with-Power-Query.png)
Sheet3 and Sheet2 contains data from “Emp Data 1” and “Emp Data 2”. As our main purpose was to get combined view from both the sheets, so let’s delete them.
![Join Multiple Excel Tables with Power Query 23 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.23_Join-Multiple-Excel-Tables-with-Power-Query.png)
You will notice that Query type is changed to “Connection only” which is fine.
![Join Multiple Excel Tables with Power Query 24 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.24_Join-Multiple-Excel-Tables-with-Power-Query-1024x467.png)
In future, if there is new data is added in the Excel file then you just need to refresh the query to get latest data
![Join Multiple Excel Tables with Power Query 25 Join Multiple Excel Tables with Power Query](https://www.excelsirji.com/wp-content/uploads/2024/03/3.25_Join-Multiple-Excel-Tables-with-Power-Query.png)