Combine Multiple Excel Workbooks in Power Query

I got a comment on a previous post today, which made me realize I’d promised this but never posted it.  So let’s look at how to combine multiple workbooks together in Power Query, providing they have the same format.  Yes, it’s been covered before, (even linked to in the comments of the previous posts,) but I’m going to put my own flavour on it.  By the time we’re done, you’ll see how similar it is to working with non-Excel files.


For our example we’re going to assume that we have four (or more) Excel files which you can download here.  I’ve stored in a folder called “Data”, which is a subfolder of the “Combine Workbooks” folder (more on why I store them in a subfolder a little later.)

Each file has a similar structure, which looks like this:


Notice that no one has bothered to set up a table style or anything, they are just raw worksheets of data.  Having said that, they are consistent in the fact that

  • The data starts in row 5
  • Each files is set up across same number of columns
  • The column headers and data types are consistent across files

(Just as a quick note, if they DID have tables set up, that would be okay too.  I’m just demoing that it isn’t necessary.)

The End Goal

The end goal we’re after is fairly common.  We basically want to grab the data from each file, strip out the first 4 rows, and append the tables together (without repeating header info.)  This will essentially create a nice data source that we can use in PivotTables, charts and other tools.

The Process

My preferred tool to combine multiple workbooks into one data source – where it used to be VBA – is most definitely Power Query today.  And here’s the steps we need to put together to make it work.

  1. Import a single workbook
  2. Convert it to a function
  3. Import all file contents (using our function)
  4. Combine all the data

Step 2 does involve a VERY minor manipulation of M code today, but as you’ll see it’s quite easy.

Let’s Combine Multiple Excel Workbooks

Step 1: Import a single workbook

To begin we’ll go to the Power Query menu and choose:

  • From File –> From Excel –> Sales-July2014.xlsx
  • Select Sheet1 and choose to Edit it

You’ll now see your query showing in the Query Editor:


We’ll need to do a bit of cleanup here to get the data just the way we need it:

  • Home –> Remove Rows –> Remove Top Rows –> 4 –> OK
  • Transform –> Use First Row As Headers
  • Select InventoryID and SalesPersonID –> Transform –> Data Type –> Whole Number
  • Select Cost, Price and Commission –> Transform –> Data Type –> Decimal Number
  • Select Date –> Transform –> Data Type –> Date
  • Select Date –> Home –> Remove Errors

That last one might be a bit odd, but I like to do that to my date columns.  The reason is that this protects me when I stack another table and it has headers.  I know that converting text to a date format will throw an error, so I’m guaranteed that any subsequent header rows will be nuked out.

At this point we’ve got a nice tidy import that would look pretty good if we decided to land it in a workbook.

Next up…

Step 2: Convert it to a function

Converting our nice query to a function is actually SUPER easy.  To begin, while still in the Power Query editor, we need to go to the View tab and click Advanced Editor.

When we do, we’ll see code similar to this, with the highlighted portion being the most important part:


Okay, now follow carefully:  Right before the let statement at the very beginning, type:


The ()=> indicate to Power Query that this is a function, not a regular query.  And the “filepath” is the name of a parameter that we want to pass to the function.

The second part is that we want to replace the entire hard coded file path highlighted in yellow in the image above – including the quotes – with the name of our variable.  When we do, the lead three lines should look like this:


That’s all the code editing you need to do.  Let’s finalize this.  Click Done.  At which point you’ll see this:


No too inspiring or exciting really, but it IS what you want.  Final thing to do here is give the function a better name than Sheet1.  I’m going to use “fnGetContents”.  Choose your name, replace Sheet1 in the name box, then click File –> Close and Load.

If the query shows up in your Queries Pane saying “Load is Disabled”, don’t freak out.  That’s just a really poorly worded message that is indicating it is only a connection.  It will still work.  🙂

You’ll also notice that, even though we imported this file, NOTHING has landed in any worksheet.  Huh?

Oddly enough, that’s as designed…

Step 3: Import all file contents (using our function)

All right, now it’s show time.  Let’s make some magic happen.  Let’s go get all of the files we need:

  • Go to the Power Query tab –> From File –> From Folder
  • Browse and select the folder that holds the data files
  • Click OK

You’ll now end up in the Query Editor and be staring at this:


Now, normally we’d be tempted to click the double arrows beside the Content header to combine them all… but that doesn’t work here.  (It just returns the first file when we’re working with Excel files.)  So this is where we need to resort to our function.

  • Go to Add Column –> Add Custom Column
  • Enter the following:  fnGetContents([Folder Path]&[Name])
  • Click Enter
  • Right click the new “Custom” column –> Remove Other Columns

Almost done!

Step 4: Combine all the data

You should now be seeing:


Click the little double headed arrow, turn off the “Use original column name as prefix”, and click OK.

You should now have a nice table of data.  Give it a name, click Close and Load and you’re done!

Final Thoughts

Working with Excel files is pretty easy once you realize how easy creating that function really is.

One caveat though… If you store your “consolidating” workbook in the same folder as your data files, your fnGetContents() function will throw an error.  The reason for this is that your consolidation workbook will also get pulled in to the query, as well as the temp file version (prefixed with a ~).  You’ll need to filter out both files.

27 thoughts on “Combine Multiple Excel Workbooks in Power Query

  1. Great article but what happens if you have similar files but maybe a few columns are different. Will it still combine the data?

    Also the "you can download file here" link is not working. Goes to OneDrive but I only see white space with loading circle dots image towards the top.

  2. Hi Stan,

    On the link, that's weird. I tested it before I set the blog live, as I've had these issues before. What I do is create the shortcode and make sure I can access it from a browser that I know 100% is not logged in to my OneDrive. It worked at publication time, and it's working for me now as well. Not sure why it wouldn't be loading for you.

    With regards to the combination of files. If the workbook structure is different, and if nothing in the code triggers any critical errors, then yes, they'll still get combined. You may have a really odd output structure though... if columns are in a different order they will still get combined A to A, B to B, etc.. And if some sheets have more columns than other, they'll still get combined, but you'll have blank rows for the sheets that don't have the same data.

    To be fair, this technique was intended to combine files of the same type with the same columnar structure, NOT varying ones. Having said that, providing there is some logic that can be identified, we could almost certainly build a routine to combine several files with varying structures into one solid table. It would just take a lot more work, and probably some custom M coding.

  3. Pingback: Excel Roundup 20150302 « Contextures Blog

  4. Hi
    Great article, and thanks for sharing.
    Is there a way to ask the user for a folder that contains the data at time of running or refreshing the query?


  5. When I try using the "fnGetContents" command it doesn't work.
    For the formula: "= Table.AddColumn(Source, "Custom", each fnGetContents([Folder Path]&[Name]))"

    it returns that it can't recognize fnGetContents and asks if it is spelled correctly. (I've got a Swedish version and this is my translation)

    Any hints?

  6. Can you check and make sure you followed through step 2 completely? It sounds like you missed the step of renaming the query to fnGetContents in that step...

  7. Hi Ken,
    Sorry, I thought I'd get a notification when you answered and I haven't had time to look into this.

    I think my main trouble is that I haven't changed the name to fnGetContents. How sloppy of me. I'll do it again and hopefully it will work. I'll get back to you.

  8. Next question is: is it possible to do the same for .txt files or access databases?

  9. Hello Ken,

    Can you please explain advantage of creating your own function versus using PQ inbuilt function Excel.Workbook([Content]) to combine workbooks?

    I was able to do same with inbuilt function with one additional step (filter (null)) to remove blank rows.

    Thanks in advance & keep up the great work your doing.

  10. The answer to this is "it depends". If you've already built a long routine in the past and just want to re-use it, then the function may help you get there faster. If the files need to be pre-processed before being combined, then the function is definitely the way to go. If you have 2 (or more) different types of source file that you can identify based on a file characteristic (date/time, file name, path, etc), then you could cook up a solution that pre-processes them using different steps to end up in the same output format before you combine them.

    If you can work around this using filters and such though, by all means do it. Whatever works best for you is the best solution in the end. 🙂

  11. Good points Ken and thanks for getting back to me.

    Yes, I enjoy the flexibility that PQ offers and your website is a great resource for exploring these options.

  12. That is great. I am wondering if there is a way using the connection you outlined above to import directly into powerpivot as my combined data sets will have multiple millions of rows of data.


  13. @Ken Puls Since Excel has received an update last week or week before, I now ALWAYS get the following order, even when I follow your guide all over again. Nothing is changed in my source files. Everything is exactly the same. But this error keeps showing:
    " An error occurred in the ‘’ query. Expression.Error: The key didn't match any rows in the table.

    I still get all content from all excel files. But the error shown is annoying. To me it just seems like a bug in Power Query introduced by Microsoft. Do you have any ideas?

  14. Odd... I'm not seeing that issue here. What is the version of Power Query that you're running? (And which version of Excel?)

  15. Windows 7 x64 Pro.
    Excel 2013 x64 15.0.4797.1000 MSO (15.0.4797.1002) part of Office Standard 2013.
    Power Query version 2.29.4217.1861.

    I figured it out! The solution is mentioned here:

    But I had trouble interpreting it yesterday until now.

    The issue (since a week):
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    Inventory_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Inventory_Sheet)
    #"Promoted Headers"

    The 'problem' is with Source, for some reason Excel will create a new column called Index Number when I import the whole folder and it will lead to the error.

    The solution (again, I didn't need to do this last week):
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    Inventory_Sheet = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Inventory_Sheet)
    #"Promoted Headers"

    Just replace [Item="Sheet1",Kind="Sheet"] with 0 and it will work flawlessly again! I spend 4 hours figuring this out. Really happy with this easy fix!

  16. Hi there. I recently started with power query. I keep on getting duplicate data. Even when I try less workbooks (sheets).

  17. Are you filtering to only one of the items in the "Kind" column? You should choose either Sheet, DefinedName or Table. The reason is that named ranges can live inside tables and worksheets, and tables live in worksheets. If you don't filter here, you're bound to get duplicate data. (FYI, we actually cover this on pages 47-50 of M is for Data Monkey:

  18. Pingback: Filenamen beim Verzeichnisimport mit Power Query integrieren | Linearis :: BI für die Fachabteilung

  19. Hi,
    Thanks for the explanation. It works really well.
    One additional question:
    Now all my data is in one big table and I can't recognize anymore from which file it originated. For instance with monthly updates you want to analyse all together to analyse the differences.
    How can you add a column with the month belonging to that record?
    In the folder list, I already extracted the month from the file name. Now I want that to be a variable when using the fnGetContents function.

Leave a Reply

Your email address will not be published. Required fields are marked *