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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

Here is an example of using that function:

and it gives you the result simply:

Video

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

2 thoughts on “Get the List of FOLDERS only in Power BI using Power Query

  • Thanks. I did something similar to this for my SharePoint folders after reading your earlier blog post, but for SharePoint Attributes I only see: Size, Content Type, and Kind. Kind = Folder, so I filtered on that. I notice that the regular Files attributes also have Kind = Folder, so that might work to filter to folders for both SharePoint and Folders? Great articles, I wish Microsoft would make working with SharePoint/OneDrive sources easier out of the box.

    • Hi Donald
      Thanks for your response.
      I also have seen the Kind, but thought Directory is a better source. To be fair. The best I would say will come from checking the data type of the content and seeing is that a TABLE or BINARY. and that is possible with a Power Query function to check what the value data type is, but that would have made the method a bit geeky. I might write about it.
      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *