Combine or Append data in one table from multiple sources using Power Query in Excel

Suppose you have similar or related data scattered in two different worksheets or workbooks (files) or files of different programs and you want to combine them in ONE SINGLE Table!

Is it possible you asked? Yes it is possible. One way is to copy and paste data from all the places in one worksheet or cast some VBA spells may be. Yes there are ways. But with power query has made this task almost effortless.

Lets say you have income statements related data in two worksheets related two worksheets one containing actual figures and other containing budgeted figures. The structure of the data is also the same and even the columns are arranged in the same way already for us. But we want to have the data of both worksheets in one single table. And we will do it using Excel Power Query.

The biggest benefit of using Power Query to combine data in one table is that it is dynamic and thus if source data changes, you don’t have to reimport and reprocess the whole bunch. Instead you can update the data with a single click to get the data updated.

Excel Power Query is an add-in that works with Excel. It is capable of doing many amazing things but at the moment we are concentrating only on Power Query’s Append feature. So lets learn how to do it with following steps.

Download Excel Power Query

If you don’t have power query already with you then visit this link to download it from Microsoft’s website. Continue with the tutorial once you have the add-in downloaded and installed. To enable the power query tab in Excel go to Developer tab > click Com add-ins and from there check enable the power query add-in.

Download Tutorial Workbook

Download this tutorial workbook to better understand and apply the steps explained in this tutorial.

Step 1: Once the tutorial file is downloaded don’t open it. Open a blank Excel file.

Step 2: Go to Power Query tab > Get external data group > click from file drop down button > click from Excel.

Step 3: Browse to the location where you have downloaded the tutorial workbook given with this tutorial above.

Step 4: In a window just opened tick “Select multiple items” option and tick mark both actual and budget options.

Step 5: Click the drop-down arrow with “Load” button and select “Load to”

Step 6: Click the option only create connection and click Load.

Step 7: Go to power query tab > combine group > click append button

Step 8: In the first drop down select Actual and in the second drop down select Budget. Click OK button. Power query editor window will generate the appended table where you can make many changes if you like to. But for the time being just click Close & Load button.

And there you have a single table containing the data from two different worksheets of an external excel file. Now you can proceed with data manipulation and analysis the way you want.

As I mentioned earlier, the benefit of involving Power Query is that data remains dynamic and if source data changes then data in appended table will update automatically. Lets test it.

Open the file that contains Actual and Budget worksheets. Add some data to each worksheet and click save button. Now go back to the Excel file where you have appended table and have an active cell within that table. Go to Query tab click Refresh button.

See? How amazing is that! So if you are not already learning the power of Power Query then you are missing something big. Get the latest Excel and step your Excel game up!