List all Files in a Folder in Excel Using Power Query

Learn Excel from MrExcel podcast, episode 2181 - List Folder Files in Excel!

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question, someone has a list of tax invoice PDF files in a folder, and they need to get the list of all those file names in Excel. Alright, and one way to do that is to type them all, or copy and paste from Windows Explorer, but there's a great tool that can solve this. And my first question was “Well what version of Excel do you have?” Because if you happen to have Excel 2016, they're going to have this amazing new functionality called “Get & Transform Data”! Now, in Office 365, it's on the left-hand side, I think in the original version of Excel 2016 it was in the third group, alright, so just look for Get & Transform. If you're in Excel 2010 or Excel 2013 for Windows, you can download Power Query and you'll have your own tab with this exact stuff.

Now let's take a quick look at this folder, alright, I just created a fake folder here with some fake data. You'll see that there are Excel files in this folder and PDFs, I only want the PDFs, and there's also some subfolders, I don't want these PDFs, I only want the PDFs in the main folder. So C:\Budgets, I'm going to copy that, and then come back here to Excel, and we're going to say that we want to Get Data, From a File, From an entire Folder, like this, and then type the folder path in there, or use the Browse button, either one. And when you get this first screen, you definitely want to Edit, and now we're in the Power Query editor.

Alright, so my goal here, I don't need the Content, so I'm going to right-click and say Remove that column. There's my list of files, I only want PDF files, so if there's anything that's not a PDF, I only want PDFs, click OK, I can see it's just the PDF files. Ah, and then look over here, see, now they're pulling in things from just the original folder, and from the Garbage folder, so I open this up, and I uncheck everything that's not the original folder. Alright, so now I have a nice little list, and this list is, you know what, 9 records, but in real life I bet they probably have, you know, dozens or hundreds of these. Alright, I don't need any other stuff now, so I can right-click and Remove those columns.

Alright, now what I really need here is I need the folder path and the filename together. Alright, so I am going to take FolderPath and drag it to the left and drop it there, and then the magic step here: In regular Excel we would have to do concatenation for this, but what I'm going to do is, I'm going to merge columns. So I'm going to Add Column, and choose Merge Columns, the Separator’s going to be None, the New column’s going to be called FileName, and click OK, alright, so we have the folder name, the slash, and the file name, like that. Now, that's actually the only thing we need, so I'm going to right-click and say Remove the Other Columns, and then finally Home, Close and Load, and we get a brand new sheet with our data. Alright now, it comes in as a table, and so I'm going to just copy this, Ctrl+C, and then come over here to where I really wanted the data up here, and Paste Special Values, click OK. Now it's not a table anymore, it's just my pure data, like that, and now, here's the really beautiful thing about this.

So we set this up once, and wow, that took less than 3 minutes to set up, but let's come back to that Budgets folder, and let's move some stuff around. Let's take one of these Garbage records, and we'll copy it up to the main folder, Ctrl+V, alright so now there's more stuff here, there's 10 PDF files instead of 9. If I come here to where the query is, and over on the right-hand side of the screen, in the Queries & Connections, you might have to make this wider, I've made mine wider already, you'll see our budgets with 9 rows loaded. I'm going to click the little Refresh icon here, and very quickly Budgets now has 10 rows loaded. So it's picking up the new records, you set this up once, and then you'll be able to just refresh to get the new data.

Well this is the point in the podcast where I usually ask you to buy my book, but instead today I'm going to ask you to buy this book “M is for (DATA) MONKEY” by Ken Puls and Miguel Escobar. An AMAZING book that will teach you all about using Power Query or the Get & Transform Data, everything I learned about Power Query I learned from this book.

OK, wrap up from this episode: Our goal is how to import a list of filenames into Excel, if you have Excel 2016, you can use the new Get & Transform Data. If you don't have 2016, but you have a real version of Excel running under Windows, then you can download the free Power Query add-in that's for Excel 2010 or Excel 2013. It's not going to work on your Android phone, or your iPad, or your iPhone, or Surface RT, or your Mac, right, it's only for Windows versions of Excel. So we're going to start from blank worksheet, Data, Get Data, From File, From Folder, enter the folder name or Browse, make sure to click Edit instead of Load. And then on the Filter, filter on the file type to get rid of anything that's not a PDF, filter on the folder name to get rid of all the garbage subfolders. Keep only the file name and folder, so right-click those others and say Remove column, then drag the folder heading to the left of file, that allows the merge to work. Select both columns, then on the Add Column tab choose Merge Columns, type a new name, click OK, and right-click on that new column and Remove the Other Columns, Home, Close and Load, and it will give you your list. The amazing part, you can refresh the query later by using this Refresh icon over in the Queries & Connections.

Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!