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

hello and welcome to the video by Trump Excel I am so mad Bunsen and in this video I am going to show you how to get a list of all the file names within a folder and all the subfolders within it so for example here I have this folder D folder or test folder where I have these files but I also have these subfolders which in turn have files now what if I want to get the names of all these files and all the files within the subfolders so to do that what you can do is you can use power query in Excel now if you're using Excel 2010 or 2013 then you would have to install the power query add-in so you will have to download and install this add-in and if you're using Excel 2016 then you already have power query in the ribbon and I will show you how to access it but if you are using Excel 2010 or 13 here are the steps that you need to follow if you already do not have power query you go to any search engine type download power query and in the first result you would be taken to a page from where you can download power query add-in once you have downloaded it and once you have installed it you would see power query tab in Excel if you do not see that tab let me quickly take you through the steps that you can use to activate power query add-in now I'm showing you these steps in Excel 2016 but these would be the same for 2010 and 2013 so the steps is you go to the file tab here you click on options in Excel options you click on Add Ins and when you click on Add Ins here in at the bottom you have manage add-ins drop down here select com Adan's and click on go and in the Commons dialog box you would see Microsoft power query for Excel because you have downloaded it installed it here is where you can activate it so you simply check this box click OK and then you will see power query here if you still don't see it here simply restart Excel applications and you would see this here now once you have the power query add-in in store or if you are using Excel 2016 you would have the power query option within the data tab and here you have the gated transform group and within this group you have all these options which are the options of power query now let's see how to get the list of all the filenames within the folder and subfolder using power query so to do that I would click on new query go to from file and click on from folder and here it will open the folder dialog box where I would have to give the name or the location of the folder which would be the folder path so to do that I would click on browse and here I would go to the folder which is T folder and I would click ok so it automatically inserts the folder path I click on ok and when I do this it will open a window then show me the names of all the files so here I have this window open and it shows me this which is this column which has the name of all the files note that it has the name of files in the main folder as well as the subfolders it shows me the extension all these additional columns of date access to modified and created and it shows me the folder path here so I can identify that the first four files are from the main folder but the remaining two files are from the subfolders now if you want this information as is in Excel you can simply click on load see what happens when I click on load it will create an Excel table and put all that data in that Excel table so here I have all these columns where I have the name extension and all these columns here but if you want to let's say remove a couple of columns or you want some additional metadata about each file for example if it what's the size of the file or if it is read-only or not then you can do this within power query editor so let me do one thing let me create a new sheet here and show you how to delete columns or add additional columns so I'm go to data new query from file folder here I would have to browse for the folder which is t folder here click okay and in the window that opens up instead of clicking on load I would click on edit and see what happens when I click on edit it will open the query editor and within this query editor I can transform the data or I can change the data before I load this data into Excel so I can change the data within Excel as well but here I get a lot more options so for example in this editor you can see I have these columns if I want to quickly delete these columns I would hold the control key delete these date columns right click go to remove columns and these columns are gone now if I want additional metadata for each file I have these this column of attributes and if I click on this icon it will show me all these additional data points additional columns that I can get for each file so in this case let's say I want size and I want to see if the file is hidden or not and I want to see if the file is read-only or not then I can click on these click OK and it will show you the data in the power query editor itself if you're satisfied with the data and you think this is good to go simply go to close and load and when I do this it will load this data as an excel table in my excel worksheet so I have the name I have extension I have all these attributes that I selected these three columns and I have the folder path now I can do whatever I want if I want to only let's say filter excel files I can select dot xlsx and it will give me all the excel files or I can do anything if I want to understand what are the files from the main folder and what which are the files from the subfolders then I can do that as well so you can see how quickly we were able to import all the file names from the folder and all the subfolders within that main folder using power query so that's it in this video I hope you found this useful thank you and have a nice day

Channel: TrumpExcel

Views: 96,291

Keywords: List of file names, List of file names in Excel, Power Query, File Names using Power Query, Get List File Names in Excel, Excel Tips, Excel Tricks, Excel Tutorials, Excel How-to Tutorials, Excel How-to, Learn Excel, Get and Transform, Get & Transform, trump excel videos, file names from folder and subfolders to excel, how to create list of file names in folder and sub folder

Id: GO-Q8j2lW2s

Length: 6min 28sec (388 seconds)

Published: Tue Dec 19 2017