Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Power Query - Import Multiple Files From Folder -- Featured Image

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

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
Get data From Folder

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

Select folder path

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.

Edit import settings

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.

Table after importing to power query

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
filter extension equal to .xlsx

Fill in .xlsx -> click ok.

Import Multiple Files from a Folder with Power Query - 7. Filter Equals

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 – “.

Filter output in table

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

  • Filter column Name -> Text Filters -> click Begins with…
File name beginning 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.

File name start with description "Data"

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”.
Expand column called 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.
Select productsales table

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.

Show Queries

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.

Show filter pane

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…

Close and load to location

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
Load table to worksheet as table

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

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

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!

Share on:
  1. Hello there. I am having trouble when importing multiple files from a folder with power query. All Files have similar columns but when one cell in a file has line brake (example below) the output is messed up. Any ways to fix this? Thank you.



  2. Hello Rick!
    I have a weekly file with 12 weeks forecast. I would to create a “database” on power query where I can collect all of the weekly files and so we can compare the changes of forecast. The main problem is the headers as you say power takes the headers from the sample file, is there any way to keep of all of the columns from all of the files?
    The Headers looks like this: material, plant, week 1, week 2 etc and each week we get a new week and the past week is deleted from the new file.

  3. Hi – do you know if there is a maximum number of files you put into a folder so power query can retrieve the data. I can’t work our why my latest file is not being imported to power query (it is the 29th file – each files has just over 30,000 rows in it, so still only just over 900,000 rows in total)

    • Hi Anita,

      Great to have you in the Power BI Field! As for as I know, there’s no maximum amount of files that the ‘import from folder’ functionality retrieves. Make sure to keep an eye on the time it takes to import though. Importing your files from your desktop is much quicker than retrieving it from a network folder.

  4. Hi Rick,

    Great Lecture!

    Just one question, given the data format used, will you be able to get date table to use it on Power Pivot?


    Josep Maria

  5. Hi Rick,

    Really great post, thank you!

    How would I do the same process, but have a query that works if the sheets do not have the same name in all the workbooks?
    I basically have 2 workbooks each month, with 2 different sheet names, which I want to combine each month.
    I can pick up all the right workbooks, but only 1 sheet name.



  6. 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 ###’. The ### 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:

        #"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:

        #"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,

  7. 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 ###. 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!


  8. 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 🙂


Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.