Import Multiple Files from a Folder with Power Query

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 formulas, prone to error. Regardless of 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.

Table of contents

• Example Files
• Import Files from a Folder
• Select Files to Combine
• Instruct Power Query how to Combine Files
• Import from Folder: Consolidation Magic!
• Conclusion

Example Files

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

To get started, open an empty file.

Import Files from a Folder

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

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

The below picture shows all the files available in the source folder. For consolidation, we need the sales data for the months of 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.

The three options to consider in the 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 always choosing 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 on the screen below. It displays information on the available files in the source folder. This is the 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.

To prevent errors, it’s good practice to instruct Power Query on 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

Fill in .xlsx -> click ok.

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 built having in mind that the files that need to be consolidated start with “Data – “.

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

• Filter column Name -> Text Filters -> click 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.

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”.

Instruct Power Query on 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 on 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.

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 a data source, Power Query imports all data from the worksheet. This option has a bigger risk of containing unwanted data somewhere in the worksheet.

Import from Folder: Consolidation Magic!

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

Note: in the 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.

The above table contains the sales data from the months of 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.

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

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

Conclusion

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”!

I hope this was valuable for you. If you want to learn more about Power Query, also check out how to Create Unique Combinations and how to Create the Ultimate Calendar Table with a free script. Hope to see you back again soon!