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’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I browse to select my ‘Documents’ folder.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I then choose to load my data to Power Query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
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("C:\Users\kathr\OneDrive\Documents")
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
Since this column holds a record, I need to expand my data by using the icon next to the column title.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
The attribute I am interested in, is ‘Directory’, so I only choose this option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
Now I can clearly see which rows are columns, and I can select only those columns where Attributes.Directory
is TRUE.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
Clicking ‘OK’ gives me the rows I want.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/178/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I have a list of the folders in my documents folder.
Come back next time for more ways to use Power Query!