Power Query: Folders Only

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
is TRUE.

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!