Power Pivot and Power Query for Dummies

Need to get a quick ramp-up on Power Pivot and Power Query? Good news…A new book just dropped on Amazon.

Learn how to:

  • Create your own database right in Excel with Power Pivot
  • Make pivot tables that use multiple data sources
  • Scrape websites and import hard-to-reach data with Power Query
  • Simplify and automate data cleanup and transformation
  • Create your own powerful formulas and functions to go beyond simple data imports

Now available on Amazon:

“Discover how Power Pivot and Power Query not only Read more

Editing Shape Points to Create Custom Graphics

I’m working on updating my Dashboard Tools Add-in to add more modern looking infographics and maps. I’ve discovered a neat feature called Edit Points.

When working with a shape, you can right-click and select Edit Points

This places little dots all around the shape. You can click and drag these dots to create a custom shape.

 

Here, I took my newly created shape and Read more

Understanding the MOD function

After weeks of struggling to get this blog upgraded to the latest version of WordPress, it’s finally up and going again. I’ve had to constantly engage in hand-to-hand combat with WordPress over the last few months. I think I’ve beat it into submission, but who knows what nonsense will pop up next. I’ve come very close to completely closing up shop and becoming a monk.

But enough complaining. Back to business.

Erin (One of my 12 fans) asked me to explain the MOD function. She says:

“I’ve see MOD used in one form or another and I just take for granted that it works. But what does it exactly do?”

 

The Basic Concept behind MOD

MOD is actually Excel’s name for a mathematical term – Modulo. A Modulo is the integer-based representation of the remaining number of units after performing a division operation.

Let’s use some Excel shape art to help illustrate.

Say that I have 8 pieces of bacon. If wake up only one of my kids for breakfast Read more

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

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