fbpx
Search
Close this search box.

Combine multiple Excel files using Power Query [Full example + download]

Share

Facebook
Twitter
LinkedIn

Say you want to combine multiple Excel files, but there is a twist. Each file has few tabs (worksheets) and you want to combine like for like, ie , all Sheet1s to one dataset, all Sheet2s to another dataset…

To make matters interesting each sheet has a different format.

What now?

Of course Power Query to the rescue.

power query man

This is an advanced example of Power Query. If you are a beginner, start with these pages.

Combine multiple Excel files – the problem

Imagine you work in Finance. Your job involves paying employees for their business travel expenses. Every time someone goes on a business trip, they submit a trip expense report. This is an Excel template with two tabs.

  • Travel details tab: for gather personal and travel details
  • Expense details tab: for itemized expense details

As you have a lot of employees, you don’t want to manually scan the files and combine the data. Here is a sample of how these files look.

personal details & travel details - sample data
Tab 1 – Travel details
expense details - itemized - sample data
Tab 2 – Expense Details

You want to combine all the expense files in to one big, consolidated & refreshable travel expense workbook.

combine multiple files using power query

Using Power Query to combine files

"from folder" - Power query

Some of you may already know Power Query’s “Get data from Folder” feature. This helps us easily get & combine multiple excel files in a folder. Unfortunately, this alone will not be helpful for us as our file has two different tabs and we need to combine them separately 😉

Here is the process we need to follow.

Start by placing all the expense reports in to one folder. This can be a folder on your computer or on a network / shared drive.

Now go to “Get Data > From File > Folder”

folder path screen - power query

Point to the folder path and Power Query will show all the files in that folder.

Once satisfied with the list of files (don’t worry if you need to exclude some files, you can do that while editing the query by applying filters), click on “Combine & Edit”.

combine and edit option - files list

Now you will get another screen asking you choose which tabs / tables you want to bring. As we have two sets of consolidations, let’s start with the first one – travel details tab. Select that and proceed.

combine files screen with sample data from one file - power query combine files

At this point, Power Query will create a folder called “Transform sample” and places a few things in it. PQ will also create a query for all the merged data. This is how your Power Query window could look.

editing transform sample - powerquery combine files

Editing the Transform sample query

As you can see, the default combined query data can be useless for our situation. So let’s proceed by editing “Transform sample file from reports” query.

What is Transform sample really?

In this sample query, you can make any changes and PQ will apply them to all the files in the folder before combining them to one gain data set.

Steps to turn travel details to a table

Our travel details sample needs to become one row table so that we can effectively merge multiple files. To do so, follow these steps:

  1. Remove blank / heading rows on the top.
  2. Remove any nulls or unnecessary rows from column 1
  3. Transpose the table
  4. Promote first row to headers

This is how the output would look after the process.

transforming travel details to a tabular format - steps required

Combine all files

Now that we have edited transform sample, time to go back to the “reports” query to see the output. If you are happy with it, rename the query and load it in to Excel (or Power BI).

Combined travel details

combined data

Combining expense details

The process is same for expense details consolidation. Start by creating a fresh “from folder” query. As expense details are in a table, there is no need to do any additional changes to the transform sample. Simply combine everything from “expenses” tables and you are done.


Combined expense details

combined data - expenses

Download sample files to practice this

Power Query can be tricky to explain with blog posts alone. That is why I made few sample files and consolidated workbook. Click here to download everything.

Try to merge the files in “reports” folder using your own logic / transformation steps. Share your story / tips in the comments.

I get an error when merging data from files

There are many reasons why Power Query may show an error when connecting to a folder. Here is a check list to help you.

  • Make sure the folder path is valid and accessible. If you created the query on one computer and try to refresh it from another, chances are it won’t work. Use shared network drives or change path in Power Query steps before refreshing.
  • Files are loaded, but merged query errors. This can happen if you edited the transform sample. Usually Power Query adds “Changed type” steps automatically after you do something. These changed type steps refer to column names in the query and change data types. If you edit the transform sample and alter the column structure of table, then the query will fail. The solution? Simple, delete all the automatically added changed type steps.
  • Some files should not be loaded, but they load and mess up the results. Before making any transformations, set up filters based on file type or names. This way you can prevent loading unnecessary files.

Do you merge / combine files with Power Query?

I do this all the time. My recent win was to merge 24 PDF credit card statements (2 types of cards over last 12 months) to one big table of data so that I can see trends and find out where I spend most.

What is your experience with combine multiple Excel files / folder query feature? What are some of your favorite tricks with this? Please post them in the comments section.

This article is inspired from a comment by Sourav.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

21 Responses to “Combine multiple Excel files using Power Query [Full example + download]”

  1. Kelly says:

    Chandoo- can you share your solution for combining 24 PDF documents as mentioned above.

    Do you merge / combine files with Power Query?

    I do this all the time. My recent win was to merge 24 PDF credit card statements (2 types of cards over last 12 months) to one big table of data so that I can see trends and find out where I spend most.

    I have a similar problem that I am solving for. Thanks

    • Chandoo says:

      Hmm, I will need to find some generic PDF dataset as I don't want to share my credit card statements online 😀
      But here is the process, if you want to try.

      1) You need to use Power BI Power Query (as Excel PQ doesn't yet support PDF import)
      2) Place all PDFs in a folder and connect with "From Folder" query
      3) PQ will detect structured tables in your PDFs. Select the correct one.
      4) Edit transform sample so you can change the results or multi-select tables and append them to one big table.

      Once the final query has what you need, just select entire table in PQ, press CTRL+C and paste it in Excel for further analysis or leave in PBI for visualizing.

      Give it a try

  2. Nam says:

    Is there a MAcro Language that can launch the Power Query?

  3. Pradeep Nair says:

    Hi Chandoo, My problem with Power Query is that my individual sheets are password protected and hence will not append. How can you solve this?

  4. CandyManLR says:

    Great tutorial!
    I managed to upload the files, but if I move them - I can't make it work after changing the source. Any specific steps to follow?

  5. Badkube says:

    This is a very useful post. However what I am trying to do something similar but unfortunately all my files are in different locations, not a single folder. I can produce a table with a list of all the file paths, how could I use this to pull in all the relevant spreadsheets?

  6. Rohit says:

    my data have alternative blank rows but the power query did not import all the rows.

  7. Ahad says:

    Hi Chandoo.

    Thank you for great website.

    Wondering power query can get data from works books located in diffrent folders and combine them together.
    I cannot copy them over to the same folder
    Regards
    Ahad

  8. Dan says:

    I have a Data Model created using the folder query method. Worked great to create the initial model. Now, every day a new file is added to the folder. How do I update the data model with just the new file (takes too much time to update all files every time).

    This can't be a unique question if people are using Power Query on folders, but can't find this online.

    Thanks

    • Chandoo says:

      Hi Dan...

      This process is called "incremental refresh" and it is not available in Excel as yet. It is part of Power BI premium functionality. One way to mimic this is...
      Split your query in to two - old files and new files.
      Set up such that all the old files are in old files folder. Build your queries.
      Now, use Append queries to combine both sets of data.
      Add files to new files folder every day and refresh that query alone.
      Once a month, move all new files to old files folder and refresh all queries.

      I know it is tedious, but I can't think of another way to apply this idea.

    • Hui... says:

      @Dan
      Why not have an import Worksheet
      And have some code that saves todays values as values at the base of a Data storage Worksheet

  9. Daniel Paul says:

    Hi, How did you get the 'From Folder' function in Excel Power Query? I do not see this as an option. This example looks like Power BI, not Power Query. Right?

  10. Roger Grossi says:

    Sorry i was not able to get this to work at all. From the very beginning I could not get the 2 files I had to show up to be combined- All that show up was only the first tab. Not sure if you have a visual step by step approach that might be easier to follow.

  11. Emerson Misael Garay says:

    Cual es mejor, PowerQuery vs PowerPivot??

  12. Chaim Kalish says:

    I’m trying to combine folder items which have different page ranges - but the exact same column headers - depending on the file. Any thoughts on how to set that up?

  13. Neeta says:

    Hi Chandoo,

    I need to combine some files which are saved in different folders on the network. I was wondering if you can suggest a solution for that. Files will have almost similar structure howevery I cannot move them in the same location.

    Appreciate your help.

    Kind regards,
    ND

  14. Maggie says:

    How many excel files in a folder can power query combine into one dataset.

  15. Edgar Sobremonte says:

    I am practicing the given worksheets example that you have provided.
    At some point, under applied steps PQ initiate "promoted headers" automatically, If I remove "promoted headers", the "reports" from query pane gets an error. It says

    Expression.Error: The column 'ACME Inc. Employee Travel Expense Report' of the table wasn't found.
    Details:
    ACME Inc. Employee Travel Expense Report

  16. Xav says:

    I'd love to practice this example, but I can't get very far. I think the problem stems from the very beginning before any steps or transformations have taken place. I can see on your "Combine Files" photo, which shows the sample "travel details" file, that you have two columns (Column1 and Column2), with Acme Inc Employee in row 1.

    I don't have that. My sample "travel details" file shows Acme Inc Employee as the column 1 header; the column 2 header is Column2. As soon as I move on to any step that removes Acme Inc Employee as the column 1 header, I get this error message:

    Expression.Error: The column 'ACME Inc. Employee Travel Expense Report' of the table wasn't found.

    I don't see that type of error addressed in your error list. What would be the solution to this error? Thx.

  17. Ana Biya says:

    Hello Chando,
    I successfully uploaded my multiple Excel to power query, however, I selected the combine and edit option, and then I clicked on the double down arrow to combine the file. but I can't get the combined file yet. please help me once.

Leave a Reply