In the last post, we saw how to import all the files from a folder, which is a fantastic time-saving technique. While focusing on this aspect, we skipped over another useful part of Power Query; which is being able to obtain a list of all the files in a folder, and sub-folders, along with the file attributes.
I have previously written posts about how to list all the files in a folder using VBA and also using a legacy Excel 4 function. These posts still receive lots of search traffic from Google, so I know this is an area which users are interested in. However, in Power Query, we now have the easiest method of all.
List all the files in a folder (and sub-folders)
In Excel, click Data -> Get Data-> From File -> From Folder.
In the Folder window click Browse…, navigate to the folder then click OK.
When the list of documents opens click Transform Data.
The Power Query Editor opens and shows some useful attributes:
- Name
- Extension
- Data accessed
- Date modified
- Date created
- Folder Path
Move the Name column to the end, then select the Name and Folder Path columns, click Add Column -> Merge Columns.
On the Merge Columns window, choose no separator and click OK.
A new column is created with the complete file path. Make the following transformations:
- Rename the new column.
- Remove all except the new column.
The Preview window shows a list of all the files and folders.
Click Home -> Close and Load to push the data into Excel.
How easy was that!
File attributes
In the section above, we saw some file attributes, mostly based on dates, but this is just the start. The Attributes column in the Preview Window provides access to lots of other information.
Click on the expand icon in the header of the Attributes column, then click OK to see the file attributes. I’m not going to lie… I have no idea what some of these attributes are, but others very useful, such as:
- 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, or the files which have not been opened recently, or duplicate file names. Once you have this list, you can decide what to keep and what to delete.
Worksheet attributes
Attributes don’t finish at the file level either. Follow the steps above to navigate a folder containing Excel files. Filter to include only the Excel files you’re interested in. Your Preview Window might look like this.
Click Add Column -> Custom Column.
In the Custom Column window to enter the following settings:
Column Name: ExcelFile
Custom column formula:
=Excel.Workbook([Content])
Click OK.
Click the Expand icon on the ExcelFile column to drill into each file.
The attributes here provide:
- Worksheet name
- Kind – worksheet or table
- Hidden – whether the worksheet is hidden
In conclusion
There you have it. A useful little trick to get information about files. Even today, I used this technique to check whether an automated process exported the same files as last month. Now I can click refresh and this check is completed in seconds.
Power Query Series Contents
- Introduction
- Import Data
- Data Refresh
- Edit Queries
- Close & Load Options
- Using Parameters
- Basic Transformations
- Combine / Append Queries
- Import All Files in a Folder
- List All the Files in a Folder and File Attributes
- Import Data from the Current Workbook
- Import Data from the Web
- Unpivot Data
- Unstacking Data in a Column
- Lookup Values using Merge
- Change the Source Data Location
- Formulas
- If Statements for Conditional Logic
- Grouping and Summarizing Data
- Custom Functions
- Common Errors & How to Fix Them
- Tips and Tricks
Don’t forget:
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet 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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- 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:
