Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

List files in a folder & subfolders with Power Query

Files in a folder featured image

The blog archives contain posts detailing how to list files in a folder (including subfolders) using VBA and legacy Excel 4 function. These posts receive a lot of search traffic through Google, so I know this is an area people are interested in. Using Power Query to list files in a folder is the easiest method of all. Therefore, in this post, I want to show you how to do this.

Watch the video

Watch the video on YouTube.

Why is Power Query best to list files in a folder?

As noted above, other methods exist for listing files in a folder. However, those methods have some drawbacks:

  • VBA Macros – The VBA code can become complex and too tricky for Excel users to understand.
  • Excel 4 Macros – These are automation techniques originally found in Excel in the 1980s and 1990s. Microsoft no longer supports Excel 4 Macros, as VBA replaced them in 1995.

In comparison, Power Query has an easy-to-use, point-and-click user interface; there is no coding or complex techniques required. This makes it the perfect solution.

Power Query also has the added advantage of easily obtaining other file and folder attributes – file size, date created, date modified, etc. While some of these attributes are available using other solutions, they are not as easy to obtain or as comprehensive as Power Query.

List files in a folder (and sub-folders) with Power Query

Let’s start by looking at how to list the files in a folder using Power Query.

In Excel, click Data > Get Data > From File > From Folder.

Get Data from Folder

Navigate to the folder, then click Open.

Depending on how many files are in the folder and subfolders, this may take some time.

A preview list of documents opens. Take a look; this is already starting to reveal some useful information.

Preview list of files in the folder

Click Transform Data to open the Power Query Editor.

The Power Query Editor opens and shows the file attributes:

Power Query Preview Window

If we just want a single list of files in the folder, we need to combine the Name and Folder Path columns.

Select the Folder Path column, hold the Ctrl key, then click the Name column. Both columns should now be selected. The selection order is important as this determines how the text is combined.

Click Add Column > Merge Columns.

Add Column - Merge Columns

On the Merge Columns window:

  • Choose separator as –None–
  • Make the new column name Full File Path,
  • Finally, click OK.
Merge Columns Dialog Box

A new column is created with the complete file path. 

We no longer need the other columns. With the Full File Path column selected, click Home > Remove Columns (drop-down) > Remove Other Columns.

Remove other columns

The Preview window now shows only a list of the files and folders.

Full File Path list in Power Query Preview Window

Click Home > Close and Load to push the data into Excel.

BOOM! How easy was that! We didn’t need to write a single line of code 😀.

List files in a folder, but not Subfolders

While working through the previous example, you may have noticed the output includes files in the selected folder AND subfolders.

But what if we only want the files from the folder? With a small change to the M code, we can achieve this too.

By default, Power Query users the Folder.Files function. This includes subfolders.

Source step showing Folder.Files M code function

The M code formula for the Source step is:

= Folder.Files("C:\Users\marks\........\Documents")

All we need to do is change Folder.Files to Folder.Contents, and we’re done. Folder.Contents only looks at individual folders.

= Folder.Contents("C:\Users\marks\........\Documents")

The Preview window should now only displays the files and folders in the selected folder.

BOOM! That was pretty easy too!

Listing file attributes

In the section above, we removed the file attributes. Instead, we may want to keep the file attributes.

When the data first loads, the available attributes are:

  • Name
  • Extension
  • Data accessed
  • Date modified
  • Date created
  • Folder Path

This list is just the start; even more information is available in the Attributes column.

Expand Attributes column

Click on the Expand icon in the header of the Attributes column, then click OK to see the full list file attributes. 

I’m not going to lie… I have no idea what some of the attributes are. But contained within here is lots of information that I find very useful.

  • Kind – the file type
  • Size – the file size
  • Read-only – whether the file is currently set to Read-Only
  • Hidden – whether the file is visible

Imagine that your disk space is getting low. Using this method, you can quickly identify the largest file sizes, files that have not been opened recently, or duplicate file names. Once you have this list, you can decide what to keep and and what to delete.


Power Query is a useful and straightforward method to list files in a folder and also get attributes about those files.

I use this technique regularly to ensure automated processes export the expected files. With a single click refresh, the check is completed in seconds.

Read more posts in this series

  1. Introduction to Power Query
  2. Get data into Power Query – 5 common data sources
  3. DataRefresh Power Query in Excel: 4 ways  &  advanced options
  4. Use the Power Query editor to update queries
  5. Get to know Power Query Close & Load options
  6. Power Query Parameters: 3 methods
  7. Common Power Query transformations (50+ powerful transformations explained)
  8. Power Query Append: Quickly combine many queries into 1
  9. Get data from folder in Power Query: combine files quickly
  10. List files in a folder & subfolders with Power Query
  11. How to get data from the Current Workbook with Power Query
  12. How to unpivot in Excel using Power Query (3 ways)
  13. Power Query: Lookup value in another table with merge
  14. How to change source data location in Power Query (7 ways)
  15. Power Query formulas (how to use them and pitfalls to avoid)
  16. Power Query If statement: nested ifs & multiple conditions
  17. How to use Power Query Group By to summarize data
  18. How to use Power Query Custom Functions
  19. Power Query – Common Errors & How to Fix Them
  20. Power Query – Tips and Tricks

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

One thought on “List files in a folder & subfolders with Power Query

Leave a Reply

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