List of file names from a folder | Excel Power Query 05
Today, we want to find all file names from a folder.
Let’s see our folder. We have different kinds of files and even a subfolder. There is also an empty folder called Empty, which must stay empty. Our task is to find all files connected to Excel, which means that we will need to do some filtering (Fig. 1)
We know the path, so we can copy it, then go to Excel, and then use Power Query, which is the simplest solution I know. We need to go the Data tab (1), then to the Get Data command (2), then to the From File (3) and From Folder (4) options (Fig. 2)
In the new window, we can find our folder, for example, by the navigation bar, where we can paste the whole path and press Open (Fig. 3)
We can see that Power Query has prepared a whole batch of information from the given folder, as well as from the Subfolder for us. We don’t see the Empty folder, because there aren’t any files. In our situation, it’s fine for us. Then, we need to click on the Transform Data button (Fig. 4)
The Transform Data option gives us the chance to add some filtering to get the data we are interested in. In the window that appeared, we can see that we can filter the information by the Folder Path (1). If we are interested only in the main folder, we can check only the main folder (2) (Fig. 5)
However, we want all folders, together with the Subfolder, so let’s close this filtering and go to the Extension filtering. Since we are interested in Excel files, we are looking for the ones with an .xls extension. Remember that Power Query is case sensitive, so if we have some suspicions that there may be some upper case letters, we have to transform our Extension column. We need to go to the Transform tab, then to the format command and the lowercase option (Fig. 6)
Now, we can filter this column by Text Filters and the Begins with option (Fig. 7)
We want the text to begin with .xls, which should get all extensions connected with Excel (Fig. 8)
We can see that we have our chosen extensions as well as the file names also with extensions. This is the information that we needed, so let’s right click on the Name bar, and select the Remove Other Columns option (Fig. 9)
We are left only with one column, where we have file names with extensions. Sometimes, we don’t need any extensions. In those cases, we can add one more step to Power Query. We have to select the column, then go to the Transform tab, then to the Extract command and the Text Before Delimiter option (Fig. 10)
Our delimiter is just a dot, so let’s write it (Fig. 11)
Now, we have only file names without extensions. However, let’s say that I still want those extensions. In such a case I need to delete this step (Fig. 12)
Now, I go to the Home tab, click on the Close & Load command, then the Close & Load to option (Fig. 13)
Let’s choose the target cell and select the Table radio button (Fig. 14)
Here’s the final result (Fig. 15)
