Use Power Query to Get a List of Hidden and System Directories

So here’s a nifty Power Query trick that lets you grab a listing of all Directories and their attributes.

If you don’t know, Power Query lets you get a list of files from a folder.

 

You get a nice listing of all the files in the directory you specify.

Notice in the Formula bar, the function used for the Source query step is Folder.Files.

Try changing that the

Folder.Contents function.

What you get is a similar table, but now there are Table entries in the Content column.

These entries are essentially Directories!

Click any of these tables to drill into the directory.

Click the Source step (in the Query Steps pane) to drill back up.

 

OK…That’s handy, but how do you get a list of Directories without all those Binary files?

Don’t try to filter on the Content column – that won’t work.

Scroll to the right a few columns, and you’ll see a field called Attributes. Click the Expand icon and place a check next to Directory.

This essentially brings in a field that tags each row with TRUE or FALSE.

This tag will be TRUE if the object in the Content column is a directory.

Filter on this Directory attribute for TRUE.

You end up with a clean list of Directories.

Here’s a sampling on the Directories in my Program Files folder.

 

Now, there are other useful attributes you can call out from that Attributes column: Hidden, Encrypted, System, etc.

Here, I’ve pulled out SYSTEM Directories under C:\Users\MyUserName.

I’m not saying this is the most useful trick in the world, but it sure is good for an hour of playing around your file system.

 

Before I sign off, I want to mention a new book – Excel Power Pivot and Power Query For Dummies.

Perfect for anyone who needs a clean and easy crash course on all the sweet goodness Power Pivot and Power Query have to offer.

The book hits Amazon in March, but you can pre-order now.

Can’t wait till March?

Check out a few other recommendations from the intrepid Chris Webb.

2 thoughts on “Use Power Query to Get a List of Hidden and System Directories

  1. AlexJ

    Now the trick would be to have a recursive query that extracted the folder tree. I’m gonna have to figure out a function for that.

    Don’t you think that this would be a good submenu to add to the [From Files] menu?

  2. Pingback: #Excel Super Links #5 – shared by David Hager | Excel For You

Leave a Reply

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