Power Query: Folders Only
29 April 2020
Welcome to our Power Query blog. This week, I look at extracting a list of folders.
I have decided to extract data from a folder. In my worksheet, in the ‘New Query’ dropdown in the ‘Get & Transform’ section of the Data tab, I choose ‘From File’ and then ‘From Folder’:
I browse to select my ‘Documents’ folder.
I then choose to load my data to Power Query.
Whilst this gives me a list of all the files in my documents folder and all the subfolders, it does not give me my subfolders.
This is because the M code used depends on Folder.Files(), viz.
Folder.Files() returns a row for each file it finds, but nothing for the folders. I need to use a different M function, namely Folder.Contents().
If I edit the first step to use Folder.Contents() instead, I get a different result:
I have far fewer rows, but this time I have folders. I could identify these by looking at the value in Extension, but it’s safer to look at the Attributes column.
Since this column holds a record, I need to expand my data by using the icon next to the column title.
The attribute I am interested in, is ‘Directory’, so I only choose this option.
Now I can clearly see which rows are columns, and I can select only those columns where Attributes.Directory
Clicking ‘OK’ gives me the rows I want.
I have a list of the folders in my documents folder.
Come back next time for more ways to use Power Query!