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, Read more

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 Read more

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 Read more

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.
Read more

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