fbpx

Import Multiple Files from a Folder with Power Query #1

 

In this post you will learn how to import files from a folder. Power Query makes it possible to import all Excel files from a folder. It works as follows. You pass Power Query a source folder, instruct which files to import, and consolidate the relevant data. But why would you want to do that?

Imagine you receive sales data for the last five years in 60 files. Now you want to consolidate the numbers before analyzing them. How do you handle that? In the past, perhaps you would open all files and copy-paste them into one file. Others may have a canny solution using complex formula, prone to error. Regardless the method, it would take a lot of manual work. A few months later, after receiving new sales data, you must do the same exercise again and again.

With Power Query there comes an end to this tedious and repetitive work! Let’s get started.

Example Files

I have made three example files containing sales data for the months April, May and June. Below image shows the file containing Sales data for the month April. The files for May and June are identical, except for the date column. All files contain a table named “ProductSales”.

Import Multiple Files from a Folder with Power Query - 1

To get started, open an empty file.

Import Files from Folder

  • Go to the tab Data -> click Get Data -> From File -> From Folder

Import Multiple Files from a Folder with Power Query - 2

Select the folder containing the files to consolidate -> Click OK.

Import Multiple Files from a Folder with Power Query - Browse Folder

Below picture shows all the files available in the source folder. For consolidation we need the sales data for the months April to June, as marked yellow. Next to those files, the folder contains two files that we don’t want to consolidate. More on this later.

Import Multiple Files from a Folder with Power Query - Import Overview

 

The three options to consider in above screen are combine, load and edit.

  1. Combine: this option leads to a screen where you can choose what data to combine. It skips the Edit step, and gives you no control over what files to combine. Instead Combine takes every file in the folder. I do not recommend this option because it does not allow for error-proofing your consolidation solution.
  2. Load: this option will load the table as displayed above into Excel (without the actual sales data).
  3. Edit: after clicking Edit, a new screen appears. In this screen you get the opportunity to change what files you want to consolidate.

I recommend to always choose Edit to filter out unnecessary files. In the next section you will learn what filters to put in.

  • For our example -> click Edit

Select Files to Combine

After clicking Edit you land at the screen below. It displays information on the available files in the source folder. This is a main step in preparing the consolidation. From here you make the selection of the files you need. You do this by adding filters. In this example, you only need the three Data files.

Import-Multiple-Files-from-a-Folder-with-Power-Query-5.

To prevent errors, it’s good practice to instruct Power Query what file extensions should be consolidated. Without you knowing, someone might add files to your consolidation folder that shouldn’t be consolidated. This may result in breaking the Power Query solution or in adding up the wrong numbers. To prevent this:

  • Click the drop-down arrow next to the column Extension -> go to Text Filters -> Equals

Import Multiple Files from a Folder with Power Query - 6

Fill in .xlsx -> click ok.

Import Multiple Files from a Folder with Power Query - 7

Tip: Even when you work with only Excel files, your source folder might contain temporary files. The names of these files start with “~” (a tilde) and have the “.tmp” extension. You best filter out the temporary files because Power Query can import these.

We are now left with .xlsx files only. Yet the file named “Notes” does not belong in the consolidation. This Power Query solution is build having in mind that the files that need to be consolidated start with “Data – “.

Import Multiple Files from a Folder with Power Query - 8

To make sure we end up with only the Data files:

  • Filter column Name -> Text Filters -> click Begins with…

Import Multiple Files from a Folder with Power Query - Filter Name Begins With

  •  Fill in “Data – “.

The instruction to only keep files that start with “Data – “, lets Power Query filter out other Excel files. This assumption is essential, and you should keep it in mind when adding future files. After all, you want the solution to keep working. Do share this assumption with your team, when working together on this.

Import Multiple Files from a Folder with Power Query - Begins with

At this stage, the table only shows the files to consolidate. We can now proceed to instruct Power Query what data to combine from the files. To do this:

  • press the two arrows in the column “Content”.

Import Multiple Files from a Folder with Power Query - Expand Arrows

Instruct Power Query how to Combine Files

The next screen shows a few options. First, Power Query asks for an example file (1). Through the example you instruct Power Query what data to extract. These instructions are then applied to all files. Since the used files are identical in structure, leave this setting at ‘First File’.

Next, you can select the data from your example file. When selecting a data source, Power Query shows a preview of the data in the example file. This can be helpful in selecting the right source. In this case:

  • Select ProductSales (2) -> click OK.

Import Multiple Files from a Folder with Power Query - Instruct how to Combine Files

Additional info:
  • The icon before “ProductSales”, with the blue bar on top, indicates this is a table. In this example, I chose to store the data in a table. The advantage of choosing a table is that as long as the table-name is the same, Power Query will find your data. Even if the table location changes.
  • The icon before “April” represents a worksheet in the example Excel file. When choosing a worksheet as data source, Power Query imports all data from the worksheet. This option has a bigger risk of containing unwanted data somewhere in the worksheet.

Consolidation Magic!

Power Query then performs the consolidation. And like magic! The data files appear combined in the table below.

Note: in below picture, more queries have been added in the left hand pane. Power Query uses these for the consolidation. For this post, it’s too detailed to handle their role. You can collapse the queries in the folder “Transform File from Power Query – From Folder [3]” so they don’t distract. For now you need to only worry about the Query named “Power Query – From Folder”.

Import Multiple Files from a Folder with Power Query - Combine

Above table contains the sales data from the months April, May and June. Next to that, Power Query added the first column with the file-names. But don’t just take my word for it!

  • Click on the dropdown-arrow in the column “Source.Name

As the drop-down menu shows, all desired files from the source folder are part of the consolidated table.

Import Multiple Files from a Folder with Power Query - Show Source Names

After importing the data, it’s important to define the right data types in the columns. In his example, the columns already have the right data types. As a last step, you often put the table with data in your Excel worksheet. To do that:

Go to Close & Load -> Close & Load To…

Click your desired output. To put your table on the Excel worksheet:

  • Select Table -> choose Existing worksheet -> specify the location for your table -> press OK

Import Multiple Files from a Folder with Power Query - Select Output

And voilá, you can now find the sales data in single table on the Excel worksheet.

 

In this post you have witnessed the consolidation of three files, but nothing stops you from replicating this for 10, 100 or 1000 files! Just imagine, the possibilities are limitless. And the best of all: when you’ve added the sales files for the rest of the year, the only thing you do to update the consolidation is press “Refresh”!

Please leave a comment below with questions or suggestions.

Rick de Groot

My name is Rick and my goal is to help you advance with Excel and Power BI. I believe learning is one of the great pleasures in life and I want to share my knowledge with you to help you improve your skills.

9 Responses

  1. Great post Rick! I ran mine and was getting errors and troubleshooted down to 2 items.
    1) xls extension files were causing an error, so I had to re-save them as xlsx (Why some are still saving to version 1997 is beyond me)
    2) All the sheet names were different and I had to painstakingly open all the files and rename the sheet the same for all files.

    • Hi Bradley. I missed out on your comment, sorry. Getting to know how to trouble shoot in Power Query is a skill. But once mastered, can definitely build very robust solutions. I’m happy you found a way to solve your problem 🙂

  2. Thanx for this tutorial. Very well explained and helpful. What would be nice to know though is what happens when you press Refesh in the end and there of some reason someone removed all files from the folder, or as it happened to us the whole folder where files were stored was renamed by mistake.

    We now get some kind of internal error Power Query pops-up and it is really hard for person that is not expert in Power Query this to know what this means.

    Any comment or additional explanation of some kind if possible to add some kind of error handling when there’s no files to load or if some of the files differ in syntax and break the flow? Something like: IF ERROR then show Message(“No files available or error in file XXX. Please contact support.”)

    Cheers Marc

    • Hi Marc. I had a similar experience. One of my clients was scared to use Power Query, due to the unclear error messages. As far as I know, there’s currently no way to introduce error handling for this.. Hopefully this will change in the future. Until then, clear instructions are the only remedy…

      Great to see you on my blog!

      /Rick

  3. Hi there,
    I am loading from folder 5 .csv files. Their structure has changed last week (from 84 columns to 115 columns) The new columns are located in the first file columns so it messes up my query steps and calculations. I did a refresh but for some reason it does not load the 115 columns, only 84 (all the new ones + the rest from the old ones). Any advice how to load all columns without redoing all my steps ?

    • Hi Mark. Thanks for posing your question. If you used the steps as provided in this post, you will have several queries. One of them is called ‘Transform Sample File from xxxx’. The xxx depends on the files you are using. If you navigate to that query, and select the Source step, you can have a look at the formula bar. (if you don’t see the formula bar, go to ‘View’ -> and tick enable the ‘Formula Bar’.

      In this formula you will notice Power Query has specified the amount of columns it will transform. For me it looks as follows:

      = Csv.Document(#”Sample File Parameter1″,[Delimiter=”;”, Columns=2, QuoteStyle=QuoteStyle.None])

      For the solution to continue working, you can delete the Columns in the formula. This is an optionial parameter. So below formula is completely valid:

      = Csv.Document(#”Sample File Parameter1″,[Delimiter=”;”, QuoteStyle=QuoteStyle.None])

      After removing the column specification (or adjusting it to 115), your query should work again. Would love to hear if that solves it for you!

      Best Regards,
      Rick

Share Your Thoughts!