Get the List of FOLDERS only in Power BI using Power Query

There are times that you need to get the list of FOLDERS and not just files. Of course folder itself (without considering files in it), doesn’t contain data to be used for a report. However, sometimes, even the folder name might contain some useful information. The Get Data From Folder option in Power BI will just give you a list of files. There is a little trick that can help you to get a list of folders. Let me show you how.

Get Data from Folder

In Power BI, or let’s say Power Query part of the Power BI, you can get data from a folder. that means it will extract all files in that folder.

The get data from a folder will give you the list of all files in that folder, and even subfolders;

Which is a good option for combining files afterward, especially if they have a similar structure.

Folders as Content

Sometimes just the folder contains some useful information. for example, I tend to create a folder for every one of my presentation engagements, and the folder looks like this:

As you can see, the folder name is an important piece of information in my scenario. There are files under each folder, but I don’t want them. I just want to get the list of folders. So how can I do that? One way is to use the Get Data from Folder, but then use the Folder Path column, and get distinct of that. then remove the folder path prefix of that. but that is too many steps. and if there is a folder without a file in it, then I would miss that folder. so let’s see another proper way of doing it.

Folder.Contents

When you get data from Folder, it uses Folder.Files Power Query function. This function get a list of all files across all subfolders.

You can change that function to Folder.Contents, which will get a list of all items only in the root folder. not just folders, but also files.

As you can see, this gives you the list of both files and folders, but only in the root of that folder (no subfolders searched).

Only Get Folders

If you are only looking for folders, then you have to filter out files from the result set. You might say, files have an extension (the screenshot above .pbix is a file). But the fact is that there are files even without extension! one easy way is to use the Attributes column and expand it to get the Directory column of that.

The Directory column will return true or false as a result;

So all you need to do is to filter the Director column to be TRUE only

Here you go, now you got the list of all tables:

What about SharePoint or OneDrive?

If your data is coming from SharePoint or OneDrive, then you can use the equivalent of this function there, which is SharePoint.Contents instead of SharePoint.Files. I have written about that function in my other article here.

Optional: Custom Function

If you do this process often, then it worth to create a custom function for it. Read my article here to learn how to create a custom function. code below is a Power Query function that gives  you list of folders from a given path:

= (path as text) as table=>
let
    Source = Folder.Contents(path),
    #"Expanded Attributes" = Table.ExpandRecordColumn(Source, "Attributes", {"Directory"}, {"Directory"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Attributes",{"Content", "Name", "Extension", "Directory", "Date accessed", "Date modified", "Date created", "Folder Path"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Directory] = true)),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Descending}})
in
    #"Sorted Rows"

Here is an example of using that function:

and it gives you the result simply:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.