15 - Get a List of File Names from a Folder in Excel Using Power Query

hello and welcome to the video by Trump Excel I am so miss Bunsen and in this video I am going to show you how to get a list of all the file names from a folder in Excel using power query so here I have this folder with the name sales data and if I open this folder you can see I have all these different files in it I have the Excel files CSV files text files now to get the names of all these files I would go to the data tab in the workbook and here in gedit I would go to from file from folder this would open the folder dialog box here and I would have to give the folder path so I can browse the folders here I would come to the folder that from which I want the file names I would select the folder which is sales data in this case click ok so it inserts the folder path here and then I can click OK now this opens a preview pane that shows me all the file names with the extension and all the different metadata so I have the date access the modified created attributes and the folder paths now you would see that there is this button called combine and we will see in the future videos on how we can use this technique to combine multiple files instantly into one single file so before I load this data let me click on edit so that this data gets opened in the query editor and I can only get the file names and I can remove the other data that I don't want so I would click on edit here it opens the data in the query editor and I have all these columns now another important column to know about is the content column you can see here it says binary now if you remember in one of the previous videos where we used the formula excel dot current workbook it also gave us a table and it gave us tables here instead of binary there was table here now what happens is if a cell pains the word binary it would mean that this cell holds a file an entire file so if I click on the space right to the binary here you can see it says East data or xlsx which means that this cell actually holds the entire file and if it says paper then it would hold the entire table and the table name would be given here in this case the file name is given here and binary holds the entire file now in this video I don't need this column so I can get rid of it what I'm interested in is getting the names of all the files so what I'll do is I would click here and if you also want the folder path you can click here as well in this case I only want the file name so I will select this column right click go to remove other columns it would only give me this single column with the findings I can if you want you can change this to file name and click on close and lo this would insert a new worksheet and load the entire data here so you can see I have all the file names here and if I go back here in the folder from which I have these file names and I add a couple of new files I can instantly refresh this data so let me do one thing let me create a copy of this and let's name it new and now when I go back here to this data notice when I update a file in the folder this is not going to update itself automatically you will have to go and refresh this data so to do that I would right click and click on refresh and see what happens instantly a new record is added for the file that we added in the folder now let me close this workbook open a new workbook here and now let me show you how you can get the names if there are subfolders within the main folders you can see I have these four files within the main folder but then I have these subfolders east north south and west and within each subfolder I have a file so if I want all the files including the files of the subfolders what you need to do is exactly the same step you go to the data tab in get data from file from folder here I need to specify the folder path which would be sales to be done with subfolders in this case select the main folder don't select the subfolders click OK click ok again if you show you the preview of all the files in you can see it's giving me the same list here this means that no matter how many subfolders you have power query is going to apply a recursion which means it is going to go in the main folder and then it is going to check within each subfolder and give you the file name so no matter how many layers of subfolders you have power query would give you all the file names using this technique now let me click on edit so this data gets opened in the query editor now what I want to do is in this case I don't want the other files I don't want CSV files and text files I only want the Excel files and if you want to do that you will have to filter this list based on the extension so if you look at this extension column you would notice that there is a mismatch here all these are in lower case but this one here east beta dot xlsx is in the upper case and this happens because if I go back to the folder when I save this file I used dot xlsx in capital and in power query is because it's very case sensitive this would mean that this is a different extension as compared to dot xlsx I know that these are the same but power query is not going to going to consider these as same so what I need to do is first I would go to extension right click go to transform and here I would select lower case so that it would instantly make all the uppercase extensions into lower case and now I can filter it so I would go to text filter begins with and here in begins with I would enter dot XLS so that it can account for dot XLS files dot Excel X dot xlsx file xlsm file XLS B file all kinds of XLS files can be accommodated with this filter so now when I click OK you can see it gives me only the excel files now I can come here right click remove other columns it only has the Excel file list and I can close and load and instantly it would insert a sheet and give me this list now if I go back to this folder and I add a new file so let me create a copy of this and I go back to my workbook here and i refresh this data see what happens it would give me the new xlsx file or any excel file that is added to the folder or to any subfolder so this is how you can get a list of all the files in a folder and subfolders in Excel using power query that's it in this video I hope you found this useful thank you and have a nice day

Channel: TrumpExcel

Views: 29,898

Keywords: get file names from folder, get file names from folder excel, get file names of all files in a folder, get filename from folder to excel, power query get file names from a folder, get list of file names in folder excel, get list of files in a folder, get list of files in folder excel, get list of filenames into excel, get list of files into excel, excel power query training, free power query course, free power query training, power query excel, sumit bansal, trumpexcel

Id: iz35tPBqBes

Length: 7min 14sec (434 seconds)

Published: Thu Apr 02 2020