Get a List of File Names from Folders & Sub-folders (using Power Query)

Watch Video – Get a List of File Names from Folders & Sub-folders

Some time ago I wrote an Excel Tutorial about getting a list of file names from a folder in Excel.

In that tutorial, I showed various ways to get the list of file names from a folder (using the FILE function and VBA).

However, the limitation of that method is that it can only get the file names from a folder, and not from the sub-folders within the main folder.

But you can do this using Power Query (‘Get & Transform’ if you’re using Excel 2016 or later versions).

Where to find Power Query

If you’re using Excel 2016, you don’t need to do anything extra. You will find all the Power Query options in the Get & Transform category in the Data tab.

Power query - Get & Transform in Excel in Ribbon

Note that Power Query and Get & Transform refer to the same thing.

For Excel 2010/2013, you need to install the Power Query add-in to use it (steps described below).

  1. Click here to download the Power Query add-in. Make sure you are downloading 32-bit if your Excel is 32-bit and 64 bit if your Excel is 64 bit.
  2. Install the Power Query add-in.
  3. Open Excel. If you see a Power Query tab, skip the remaining steps. If not, move to the next step.
  4. Go to File and click on Options.
  5. In the ‘Excel Options’ dialog box, click on Add-in in the left pane.
  6. From the Manage drop-down, select COM Add-ins, and click on Go.
  7. In the list of available add-ins, select Power Query and click OK.
  8. Close the Excel Application and restart Excel.

The above steps would install and activate the Power Query for your Excel.

Get a List of File Names from Folders & Sub-folders

Since I am using Excel 2016, all the snapshots and written steps are for Excel 2016. You can use the same for Excel 2010 and 2013 as well.

Now let’s see how to get a list of all the files names from a folder and sub-folders within it.

Here are the steps to get a list of all the file names from a folder:

  1. Go to the Data tab.Get File names from folder - data tab
  2. In the Get & Transform group, click on New Query.Get File names from folder - new query
  3. Hover the cursor on the ‘From File’ option and click on ‘From Folder’.Get File names from folder - from folder
  4. In the Folder dialog box, enter the folder path, or use the browse button to locate it.Folder location in Power Query
  5. Click OK.
  6. In the dialog box that opens, you’ll see the names of all the files along with other metadata.Get File names from folder - data in Power Query
  7. Click on the Load button.Get File names from folder - data in Power Query load

The above steps would load all the data about the files in your Excel worksheet.

Get a list of File names from folder - file name in column A

Once you have the data in Excel, you can edit it if needed.

With the data that I have in Excel, I can do the following:

  • Filter the file0 names based on extension (file type) –  it’s in column B.
  • Filter the file names based on the folder name – it’s in column F.

Editing the Columns Data in Power Query

Before loading your data into Excel, you can also edit the data in Power Query.

For example, you can delete some columns or get some more metadata for each file.

Here are the steps to get additional metadata columns in Power Query editor:

  1. Go to the Data tab.
  2. In the Get & Transform group, click on New Query.
  3. Hover the cursor on the ‘From File’ option and click on ‘From Folder’.Get File names from folder - from folder
  4. In the Folder dialog box, enter the folder path, or use the browse button to locate it.Folder location in Power Query
  5. Click OK.
  6. In the dialog box that opens, click on ‘Edit’.Load data in Get & Transform - Power Query Edit Button
  7. In the Power Query editor, click on the expand icon in the ‘Attributes’ column. It will show you a list of the additional columns you can get for the files (such as file size or read-only or hidden). Select the columns that you want to have in the data (and uncheck the rest).Power query - Get & Transform click on attribute
  8. Click OK.
  9. Click on ‘Load’.

This will load the data in the Excel with the selected additional columns.

You can also delete columns if you don’t need it. To do this, in the Power Query editor, select the column you want to delete, right-click, and click on Remove.

Remove column in Power Query Editor

You May Also Like the Following Excel Tutorials:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)

  • Madhab Chandra Basak

    Can we get power query in excel 2007?

    • Unfortunately, Power Query is not available for Excel 2007

      • Madhab Chandra Basak

        Thanks.

  • Sundaresh Peri

    Thank you Sumit. Very good tip using Power Query.