Tracking and managing data across multiple files can be challenging. With Power Query, you can get the file names from a specific folder and all its sub-folders. In this article, we are going to show you how to get a list of file names in Excel.
We will get help from Power Query to get a list of file names. Although Excel’s Power Query feature is for gathering and querying data, we can use it to list name of files instead of data in them.
What is Power Query?
The Power Query is a data management and querying tool for Excel. It was released as an add-in for Excel 2010 and 2013. Microsoft merged it into Excel with 2016 version. If you are using Excel 2016 or newer - including Microsoft 365 - you most likely already have this feature. To learn more about Power Query, see Power Query 101.
Using Power Query to get a list of file names
- If you’re using Excel 2016, 2019 or Office 365, follow Data > Get Data > From File > From Folder in the Ribbon.
If you’re using Excel 2013 or earlier, follow Power Query > From File > From Folder in the Ribbon.
- Enter the main folder of your files either by typing its name or using Browse. Click OK when you have the target selected.
- Next is a preview window. You can see a list of files in the folder you selected and its sub-folders. You can click the Load button to populate the full list in your workbook. Power Query also allows you to modify this data before inserting it in to the worksheet. Click Transform Data (or Edit in older versions) to proceed.
- Clicking Transform Data button opens the Power Query window with the list of names. You can,
- Delete unwanted columns, such as Content.
- Show additional information about files.
- Change the order of the names, such as ascending names or descending modify date.
- Filter by folder or file type (extension).
- Filter out hidden files or folders
- We suggest removing the Content column first and then expanding the Attributes column to organize the list.
- You can remove the Content column by right-clicking on it and selecting Remove.
- To expand the Attributes column, click on the icon at the header, de-select the items you do not need and click OK.
- You can remove the Content column by right-clicking on it and selecting Remove.
- Clicking OK will add the selected fields as columns. You can use these fields for filtering or sorting as well.
- Once you are satisfied with the result, click Close & Load on the upper left of the Power Query window.
- Excel will load the table in a new worksheet. Another advantage is that you can refresh the table or connections at any time to get the updated file names and information.
