Hi there,
Today, I will show how to combine multiple sheets into one single table/file using Power Query in Excel with a simple example.
Note: Power Query can be used as an add-in in Excel 2010, 2013, and is an inbuilt feature from Excel 2016 on wards. Based on your MS Office version, some images may look different. My example lessons and captured image used in Excel 201.
If you want to combining data from different sheets using Power Query, it’s required to have the data in an Excel Table (or at least in named ranges). If the data is not in an Excel Table, your need to convert it into table. The method shown here with my example.
Suppose you have two different sheets –as Project_cost_2019, and Project_cost_2020.
Each of this worksheet has the twelve months data in an Excel worksheets columns name Project Id, Month and Project Costs (sample as below).
Suppose you want to combined Project_cost_2019 and Project_cost_2000 data into one single table. You can easily do that in power query. Just follow below process.
Here are the steps to combine multiple worksheets with Excel Tables using Power Query:
1. Go to the Data tab.
2. In the Get & Transform Data group, click on the ‘Get Data’ option.
3. Go the ‘From Other Sources’ option.
4. Click the ‘Blank Query’ option. This will open the Power Query editor.
1. In the Query editor, type the following formula in the formula bar: =Excel.CurrentWorkbook().
Remember that the Power Query formulas are case sensitive, so you need to use the exact formula as mentioned (else you will get an error)
Hit the Enter key. This will show you all the table names in the entire workbook as below.
6. [Optional Step] In this example, I combined all the tables (2 tables). If you want to combine specific Excel Tables only, then you can click the drop-down icon in the name header and select the ones you want to combine.
7. In the Content header cell, click on the double pointed arrow.
8. Select the columns that you want to combine. If you want to combine all columns, make sure (Select All Columns) is checked.
Note: If you want to select only specific columns then just check those columns and uncheck unwanted columns.
9. Unchecked the ‘Use original column name as prefix’ option.
10. Click OK.
11. Click Close & Load option for completing this process and save it.
The above steps would combine the data from all the two worksheets into one single table as below.
Note: The last column (rightmost) has the name of the Excel source tables names (Project_cost_2019 and Project_cost_2000) where data has been combined in this single table. This is an identifier that tells us which record came from which Excel Table. In the right site under Query Settings PROPERTIES will show the name of the Query. The default name is Query1. You can rename it with a meaningful name.
Here are a few modifications you can do to the combined data in Power Query itself:
1. Change the Month column only dates format (not the time). To do this, click the Month column header, go to the ‘Transform’ tab and change the Data type to Date.
2. To change the Project Costs values into currency format. Right click on the Project Costs column header, select Change Type, select Currency from fright site sub-drop menu. Same way you can change data type and rename column title of any columns if required.
Now I have combined the two worksheets data into one single table in this Power Query example in simple and easy way.
Note: If you make any changes, even adding new data in source tables it can be update or added into your combined file. For do that
Click the Data option, click Refresh All then all source and Query1 will be refreshed as below picture.
Please subscribe my blog to getting the auto notification of my next useful tips.
Thanks all!
Happy learning.
Obayed Moni
Comments