Power Query is an amazing feature provided by Microsoft to import, transform, and extract the data from various sources such as other workbooks, CSV file formats, JSON files, folders, etc. It also allows you to connect with the folders on your PC and get their data. In this tutorial, we would learn how to get and extract all the filenames from Folder in Excel. You can also get the other folder information like – File Type, Date Modified, etc. of all the files in the folder into Excel.
The Power Query feature is inbuilt in the newer Excel versions (Excel 2016, 2019, and Office 365). You can find it this tool in the ‘Data’ tab under the group ‘Get & Transform’.
The above image is from Excel Office 365. The look and design of these power query tool may differ in other excel versions (Excel 2016 and 2019).
This feature is not inbuilt in the older versions (Excel 2010 Professional Plus and Excel 2013). For these versions, you have to firstly download and install an external free power query add-in. Once installed, a new tab – ‘POWER QUERY’ would get added to the ribbon.
You can find more information about Basics of Power Query here – Overview of Power Query in Excel.
Get File Information like Filenames from Folder in Excel
Follow the below steps to make a list of all the files in a particular folder (like filenames, file type and last date modified) in excel:
- Go to the ‘Data‘ tab and click on the option ‘Get Data‘ > ‘From File‘ > ‘From Folder‘, as shown below:
- Excel would open the ‘Folder’ dialog box as shown below. Paste the entire folder path and then simply click on OK, like this-
- As a result, excel would open the preview in a new window. To directly load previewed data to the excel worksheet, simply click on the ‘Load’ button as highlighted below.
As a result, you would see following changes in your excel workbook.
- A new worksheet (with name – Sheet#) would get added at the beginning, to your workbook.
- Excel would create a new excel table, with the following details about the files in the folder. The file name, its extension, date accessed, date modified, date created, and folder path.
- A new window named – ‘Queries and Connections‘ would appear on the right pane of excel. It contains details of the query name (in our case query name is ‘EXCEL INFOGRAPHICS’) and the number of loaded rows.
The best part of the above method is that the Power Query tool also lists the information about the files that are inside the sub-folders of the main folder.
Now, when you add any new file into the main or the subfolder, you just need to refresh the power query and the list would update automatically. The newly added file would get listed in the table, Check the below section for this refresh activity.
Automatic Updation of Excel List – Power Query
Let us add six new files to the sub folder named ‘Compilation’. See the image below.
Now, go to the Excel file that contains the existing file list (194 rows of entries), created using the above power query technique.
Click anywhere on the excel table containing a list of the files from the folder. As a result, a new ribbon tab named ‘Query‘ would appear on your screen.
Go to this new ‘Query‘ tab and click on the ‘Refresh‘ button.
You can find more on the ‘Multiple Ways to Refresh Power Query in Excel’ in this link.
As soon as you click on the ‘Refresh’ icon, the excel would again perform the steps stored in the Power Query connections. Now, check the ‘Queries and Connections’ window to the right of the excel window. What change did you see?
You would notice that the number of rows loaded to excel gets updated. There are now 200 rows (as against the previous 194 files). This denotes that excel has incorporated the newly added six file information in the excel list now.
Check the table and you get the updated file list from the folder in Excel.
Write your views and thoughts on this tutorial in the comment box below.