This is the second post of a series that covers everything about importing all files in a folder. Click here to see the series of post.

In this scenario I’ll show you to import all sheets from all excel files in a folder.  If you want to follow along, download the files from this link.

Important: I’ll show how to import all sheets from all Excel files in a folder, however, it is the same process if the files contain just one sheet.

Step 1: Import the files from the desired folder

Go to Data -> New Query -> From File -> From Folder

Get and Transform Excel 2016 - Import data from folder 

Click on ‘Browse’ and browse for the folder that contain the files, then click OK.

Once you click OK, press Edit on the next window.

Then you’ll see a window with several details of each file in the folder such as Extension, Date accessed, Date modified, etc.

We’re only interested in the Content column, therefore, right-click on the header of the Content column and select Remove Other Columns.

Get and Transform Excel 2016 – Remove Other Column

The go to Add Column -> Add Custom Column

Get and Transform Excel 2016 - Add Custom Column

 

Step 2: Extract the contents from each file

In the next dialog box, type a name for the new column (e.g. FileContent) and the custom formula: =Excel.Workbook([Content], true) and click OK.

 

Note: The second argument of the function Excel.Workbook is to promote the headers from each sheet. If you don’t set this argument to true, you’ll have to promote the headers afterwards and then filter out the headers from the data.

Then click on the column and click OK in the Expand dialog box.

Get and Transform Excel 2016 - Get the contents from each file

 

Then go to the filter button of the FileContent.Kind column, select Sheet and click OK.

Get and Transform Excel 2016 – select Sheet

Right-click the header of the FileContent.Data column and select Remove Other Columns.

Then click in the Expand button in the column FileContent.Data

Get and Transform Excel 2016 - Expand contents of column

In the next dialog box, make sure to uncheck the option “Use original column name as prefix” and click OK.

Get and Transform Excel 2016 –Use Original Columns name as prefix

The data from ALL SHEETS from ALL FILES should be visible now. Isn’t this amazing?

Now make any further changes needed. For example, change the format of the Date column to date (Right-click the header of the Date Column, go to Change Type -> Date)

Step 3: Load the data into Excel

Go to File -> Close & Load To…

 

Get and Transform Excel 2016 - Close and Load

Select Table and the destination of the results (New worksheet or Existing worksheet), then click on Load.

That’s All!!!

Click here if you want to see the other posts in the series.

Want to continue learning about automating your data preparation processes?? Subscribe to the blog.

3 Responses

  1. uhhh… stupid question: will the Power Query steps be captured by a Macro Recording? don’t seem to be.

    1. Hi Joe,
      Actually, that’s a very good question and one of the cool features of Power Query.

      When you perform the data cleaning steps in Power Query, these steps are “recorded” in a language called M. This allows you to repeat all your data transformation steps just by clicking on Refresh. So it’s like macro recording, but much more flexible.

Leave a Reply

Your email address will not be published. Required fields are marked *