How To Combine Files Using Power Query In Excel

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!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

4 Responses to How To Combine Files Using Power Query In Excel

  1. Garrett May 26, 2017 at 10:58 am #

    Not getting a “Combine” Drop-Down Menu when I set the file destination. Any idea why?

    • Michael Rempel May 26, 2017 at 7:50 pm #

      Not sure. What version of Excel are you using?

      • Garrett May 26, 2017 at 9:31 pm #

        2016!

        • Michael Rempel May 27, 2017 at 8:08 am #

          I’ve been searching and can’t find any reasons why you don’t get the combine option. Here is a link to various limits of Power Query: https://goo.gl/29wCxu

          Is there anything unusual about the files? Can you send me a screen shot of what you see when you get to that point? send to mrempel@excel-bytes.com

Leave a Reply