WARNING!!! This post is full of POWER.

Power Query is a great tool for importing and transforming data in Excel. But unfortunately it’s missing a key feature out of the box to make it truly useful.

In this post we’re going to find out how to import data from multiple files in a folder using Power Query. To do this we will need certain conditions to be true.

  • We’ll need Power Query installed. Read this post to find out how to install it.
  • The data structure in each file must be the same.
    • Same number of columns with the same column headings.
    • Data is on the same sheet name in each file.
  • We will need all the files we want to import located in the same folder.

Step-001-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

For our example, we’re going to import 4 Excel files all from this location.

C:\Users\John\Google Drive - Excel\Excel Website\Power Query\How To Import All Files In A Folder With Power Query\Example

Step-002-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

Each file is structure the same way. The column headings are all the same and in the same order. The data is in a sheet called Sheet1 for each file. Only the data is different for each file.

Step 1: Setting up the import query for one file.

Step-003-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

Now we will set up the import query for one file.

  1. Go to the Power Query tab in the ribbon.
  2. In the Get External Data section press the From File button.
  3. Choose From Excel in the drop down.

Step-004-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

Now choose a file to import. You can choose any of the files in this step as we are creating an import query that will work for all the files and all the files are structurally the same.

  1. Navigate to your folder and select any file.
  2. Press the OK button.

Step-005-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

A preview of your data will appear in the Navigator window.

  1. Select the sheet that contains your data. In our case Sheet1. Check the data preview to make sure you have the correct data.
  2. Press the Edit button.

Step 2: Edit the query.

Step-006-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

Power Query will create the import code (in a custom language called M) behind the scenes, but we will need to edit it slightly.

  1. Go to the View tab.
  2. Press the Advanced Editor button.
  3. You will now see the code that has been created for your import.

Step-007-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

We can now edit the M code.

  1. Add the red bits of text into your code.
    
    let GetFiles=(Path,Name) =>
    
    let
        Source = Excel.Workbook(File.Contents(Path&Name), null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer ID", Int64.Type}, {"Order ID", Int64.Type}, {"Item", type text}, {"Quantity", Int64.Type}, {"Unit Price", Int64.Type}, {"Total", Int64.Type}})
    in
        #"Changed Type"
    
    in GetFiles
  2. Press the Done button.
  3. Rename the query to functionGetFiles.
  4. Go to the Home tab.
  5. Press the Load & Close button.

We have just changed the query to a function that takes a file path and name as inputs then returns the data from that file path and name.

Step 3: Create a query on the folder containing your files.

Step-008-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

Now we will create a query on the folder containing all our files.

  1. Go to the Power Query tab.
  2. Press the From File button.
  3. Choose From Folder in the drop down.
  4. Browse to or enter the path of the folder.
  5. Press the OK button.

Step-009-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

This type of query outputs data about all the files in a folder such as the file name, location, date modified etc… A list of your files will be previewed and we can then press the Edit button if this preview looks ok.

Step 4: Add a column with your custom function.

Step-010-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

Now we will add a column to this query.

  1. Go to the Add Column tab.
  2. Press the Add Custom Column button.
  3. Change the name of the column to GetData.
  4. Enter this formula into the Custom column formula.
    =functionGetFiles([Folder Path],[Name])
  5. Press the OK button.

This will call the function we created in step 2.

Step-011-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

Now we need to expand the GetData column we just created to show its results.

  1. Click on the expand and filter icon in the column heading of our new GetData column.
  2. Select the expand radio button.
  3. Press the OK button.
  4. Now go to the Home Tab.
  5. Press the Close & Load button.

Step-012-How-To-Import-All-Files-In-A-Folder-With-Power-Query How To Import All Files In A Folder With Power Query

Now our data will be imported from all the files in our folder and combined into one table. If we add files to our folder later on, we can import these also by simply refreshing the query. Wow, the POWER!

  1. Here is our data from all 4 files combined into the same table.
  2. If we add files to our folder and want to import these as well, go to the Data tab.
  3. Press the Refresh All button.