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.

Four Must-Know Options if You’re Using Power Query

Over the last year, Microsoft has added a few options to Power Query that you should definitely embrace (if you’re working extensively with Power Query). These options alleviate some of the annoying quirks in Power Query. I’m especially grateful for the option to turn off that damned Type Detection feature.

In any case, here is my list of must-know options if you’re using Power Query.

Setting a Default Load Behavior

If you’re working heavily with Power Pivot as well as Power Query, chances are you load your Power Query queries to the Internal Data Model a majority of the time. If you find that you always load your queries to the Data Model, you can tweak the Power Query options to automatically do that. Open Power Query options and select, Data Load under Global, and then choose to specify a custom default load setting. This enables the options to Load to the worksheet by default or Load to the Data Model by default.

 

Preventing Automatic Data Type Changes

One of the more recent additions to Power Query is the ability to automatically detect data types, and proactively change data types. This “Type Detection” is most often applied when new data is introduced to the query.

For instance, the screenshot below shows the Query steps after importing a text file. Note the Changed Type step. This step was automatically performed by Power Query as part of its type detection feature.

 

Although Power Query does a decent job at guessing what data types should be used, data type changes that can cause unexpected issues – especially when you’re working with lots of numbers that are really identifiers or textual in nature. I frankly find the type detection feature annoying. If data types need to be changed, I’m more than capable of figuring that out myself. I personally would rather handle data type changes without the help from Power Query’s type detection feature. So…I happily turn this feature off. In the Power Query options, look for Data Load under Current Workbook, and then uncheck the option to automatically detect column types and headers for unstructured sources.

 

Disabling Privacy Settings to Improve Performance

The privacy level settings in Power Pivot are designed to protect organizational data as it gets combined with other sources. When you create a query that uses an external data source with an internal data source, Power Query stops the show to ask how you’d like to categorize the data privacy levels of each data source. Most of us deal solely with organizational data, so the privacy level settings do little more than slow down queries and cause confusion.

Fortunately, there is an option to ignore privacy levels. Select Privacy under Current Workbook, and then choose the option to ignore privacy levels.

 

Disabling Relationship Detection

When building a query and choosing Load to Data Model as the output, Power Query will, by default, attempt to detect relationships between queries and create those relationships within the internal Data Model. The relationships between queries are primarily driven by the defined query steps. For instance, if you were to merge two queries then load the result into the Data Model, a relationship is automatically created. In larger Data Models with a dozen or so tables, Power Query’s relationship detection can affect performance and increase the time it takes to load the Data Model. You can avoid this hassle and even gain a performance boost by disabling relationship detection.

Select Data Load under Current Workbook, and then uncheck the option to create relationships when adding loading to the Data Model.

Books, Old Man Skywalker, and Plagiarism

Hey there! Remember me? I’m back from a long 6 weeks of finishing up several book projects.

 

Feels weird to be back at the office. I’m getting a lot of perky people asking me how my holiday went. Mrs. Pig sent me this pic, which sums up my feelings about all that obligatory post-holiday banter.

 

Anyway, as I mentioned before, I’ve been pushing to get a load of books out the door.

Some are available now, while most will be available within the next few weeks.

 

Dick Kusleika and I updated our Access 2016 Bible.

I don’t know if it ever got to #1 in Amazon’s Database category (like Dick was pushing for), but I just checked and it #6.

 

Bill Jelen and I updated our Pivot Table Data Crunching book.

We added new content, including a new chapter on Power Query.

 

It looks like John Walkenbach’s career as Old-Man Skywalker has taken off.

So he’s passed a couple of his books down to Dick Kusleika and me.

We’ve taken over J-Walk’s Formulas and Power Programming books.

Excel 2016 Formulas was an easy update.

There were only five new functions added to Excel 2016, so not a ton of changes from last version of this book.

We both added a decent bit of new content to Power Programming.

 

I also did a couple of solo projects.

I pushed out the 3rd edition of Excel Dashboards and Reports for Dummies (new content peppered throughout most chapters).

 

Finally, I’m wrapping up a new title: Power Pivot & Power Query for Dummies.

This one is fun for the entire family; great for anyone just starting to explore the Power tools in Excel.

My book hits Amazon at the end of March, but if you can’t wait that long, I recommend picking up the excellent M is for (Data) Monkey.

Ken Puls and Miguel Escobar co-authored this book. It’s very well written and with lots of excellent easy to follow examples.

I asked Ken to email me an electronic copy of his manuscripts so I can use them for my book. He must have forgotten, because I haven’t received them yet.

 

Speaking of plagiarism…reminds me of two Stewart Francis jokes

“People call me a plagiarist…their words, not mine”

“Anyone who accuses me of stealing their jokes can kiss my black ass”

 

Ahhh….feels good to be back in the blog saddle. It’s going be a great 2016.

Happy New Year everyone.

Oh and Ken….send me that manuscript. I’ve got 4 chapters due by Monday.

Adding Dynamic Annotations for your Charts

Here’s a quick idea for anyone who needs to show annotations or comments on their charts. Tie your annotations to a check box so that users can interactively decide to show them or not.

showannotations
The annotations themselves are simply chart data labels that are showing Category names instead of values. These labels are tied to an invisible data series in the chart. This means that as your chart data changes, the annotations move with the other chart elements. The check box is a Form control that drives a simple IF formula. IF the check box is TRUE then annotation series gets plotted on the chart, else the annotations series is ignored.

This neat trick involves no VBA. It uses a few simple modeling techniques that have been around for years. In fact, this trick uses the same steps described in this post I pushed out back in 2009. Follow that post to see how it’s done.

Also, feel free to download the sample workbook to see it in action.

Lock Slicers Before Sending Out your Dashboards

Last week, I hosted my Power BI Boot Camp here in Dallas. The folks that attended were seriously one of the best crowds I’ve met. Lots of great discussion about the new era of Power BI and data science. I even learned a few new tips from some of the attendees.

Here’s a quick tip I didn’t know.

A young fella named King taught us how to lock the positioning of slicers so users can’t accidently move them around. If you right-click your slicer and select Slicer Size and Properties, you’ll see the Position and Layout section. Expanding this section will reveal a checkbox called ‘Disable resizing and moving’.

Placing a check here essentially locks your slicer into place.

Users can select items in the slicer, but won’t be able to move or resize it.

How have I never seen this setting?

Excellent tip King – Thanks!

VBA to JavaScript Translator

It’s been a while since my last post. I’ve been enthralled with updating a few of my books for Office 2016 and learning about the inner workings of Web Apps for Office.

For those of you who don’t know, the future of office programmability will be creating applications that work on all devices (desktops, phones, tablets, etc.). Microsoft is currently developing a host of web APIs that will essentially make up a new kind of web-based Object model in which developers can leverage to create web driven applications.

These “apps for Office” are NOT a replacement for VBA. So don’t go looney on me. VBA still has a home on client-side development. The new Web Apps paradigm is a way for us to be able enter a realm of development we’ve been missing out on (web development).

This will require us to jump into scary waters. We’ll have to get comfortable with JavaScript, XML, HTML and some of the other web languages that we just don’t deal with now.

This journey into web development will be a slow one for many of us. It will require think-time, practice, and training. It will be an uncomfortable, but worthwhile transition.

Don’t worry…it’s not something we need to deal with all at once. I’ll eventually do a series of posts on developing these Web Apps for Office. For now, I want to share a pet project that I’ve been toying with.

 

I’ve create a VBA to JavaScript translator; partly to help me get my mind around JavaScript, and partly as an educational tool for anyone interested in moving from VBA to JavaScript.

You can find this tool here

This tool is designed to translate the most common constructs in VBA to JavaScript.

The idea is to enter some familiar VBA code to see how you would write the same syntax in JavaScript.

 

I figure I’m about a year too early with this kind of tool, but who knows. Some of you may be looking to dip into JavaScript now.

By the way…on the tool’s web page, you will find a link to a Github page where all the source code is available. I’m hoping a few intrepid souls are willing to help enhance the tool to make it more robust.

I’ll be back for regular blogging later this week.

10 Little Known Facts about Excel’s Humble Beginnings

Excel turns 30 today!

Yes, the program that has served as the foundation for my paycheck for over 15 years was saved to a Master Disk and rushed to market on this day in 1985. Given the impact Excel has had on the way the modern world works with data, it should be a global holiday. But I suspect this day will go unnoticed by almost all of Excel’s 1.2 billion users.

 

For my part, Read more

Contest to Win a Free Seat at my Next Power BI Boot Camp

I recently appeared on ExcelTV for a riveting one-hour interview.

Some of you saw it live.

Others of you were too busy doing things like having a love-life, engaging in real friendships, and doing something interesting.

 

For those of you that did catch the interview live, you’ll know that I announced a contest for a free seat at my Excel Power BI Boot Camp in October.

That’s right! If you win this contest, you get to sit in on 3-days of training without paying the registration fee. All you have to do is get yourself to Read more