In this tutorial we’ll take a look at how to combine files using Power Query in Excel. I will be using Excel 2016 which has Power Query built into the Data tab, but Power Query is available as an add on to 2010 & 2013, so make sure you add that to your version in order to take advantage of this very useful feature. Here is a link to download Power Query from Microsoft.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a folder that contains sales data for six months:
You can download those files here.
Each file is structured identically. Here’s an example:
I want to combine these into one table in a file, then use that file to create a pivottable. Then when I add new files to that folder, have the table and pivottable easily update.
To do so, I’ll go to Data > New Query > From File > From Folder (for those with 2010 or 2013, you’ll go to the Power Query tab):
I’ll choose the location of the folder, then click OK and OK again. Excel will list the files that were in that folder, and from there I’ll choose to Combine and Edit:
Next Excel will ask me to choose which worksheet the data is on. I’ll choose Sheet1 and click OK:
Here we’ll see the Query Editor where we can make changes to the layout. The only change I’m going to make is to remove the Source.Name column:
Then I’ll click Close & Load and Excel will create a table in my workbook with the data from all six months of files in it:
I’ll create my pivottable the way I want it structured including adding a pivotchart, as follows:
Now, two months later, I want to add more data to the file. I’m going to incorporate the sales data for July and August. Those files can be found here.
All I need to do is drag those files into the folder with the other six months of data, click on the table in my workbook and choose Refresh from the Design tab. This will automatically incorporate the additional data into the table. Next, I just need to click on my pivottable and Refresh it from the Analyze tab and my pivottable and pivotchart are automatically updated:
And that’s how simple it is!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!