intro to happy — how we approach teaching (and learning) power pivot & power query

power pivot first or power query first

On day one of a typical two-day training we start our students off with a nice, clean dataset and teach them the basics of loading data into Power Pivot, building a data model, writing some DAX, making a pivot table report. We don’t start with Power Query but inevitably during day one someone makes a comment that their data isn’t very clean and probably couldn’t be loaded right into Power Pivot. Let’s be real, this fictitious person is most of us. Most of us aren’t working off clean data sources. It’s a common experience to have messy data in Excel, CSV, & XML files that doesn’t fit into Power Pivot. And even if we have a better data source, like a data warehouse, there’s still usually some transformation that needs to happen to get our data in good shape for analysis. So if our data need some cleaning to any degree then we need Power Query before we can make much progress with Power Pivot.

If it’s the case that Power Query is a prerequisite for Power Pivot, then why don’t we teach you Power Query first in order to prepare you for Power Pivot? I’ve got the answer for that. It’s the answer we’ve been telling ourselves for a while now — Power Query doesn’t really make sense until you understand the shape your data needs to be in to build a good data model in Power Pivot. Said another way, once you’ve come to appreciate what the significance of the Power Pivot data model then you’ve got the right motivation to learn Power Query. Even as I’m writing this I’m wondering if that is still the right approach.

From out in the field …

In the past two weeks I’ve been in two trainings where we’ve broken protocol and introduced Power Query very early in day one. In both of these trainings we were working with the client’s data as opposed to a canned Adventure Works database, and in both of these trainings the client’s data was really messy right from the beginning. Instead of leading with Power Pivot, we led with Power Query. The training went well but it was more difficult than it had to be and here’s why:

Whenever I’m communicating a new concept, I prefer to start with why.

The why of Power Query is “You need to use this tool to transform your raw data into the shape that the Power Pivot data model likes, in the shape of flat files, with data tables and lookup tables.” Now this statement really makes zero sense if you’re an Excel person just being introduced to these tools. There’s a lot of new information packed into this why — importantly there’s this idea of a data model which to a Power Pivot beginner is an enigma wrapped in a mystery. So without having introduced the data model, teaching Power Query comes with this disclaimer “Trust me, all this data cleaning and transformation is what you need to be doing. You’ll see later why it’s important.” Which is basically some version of “Because I said so” which has been my least favorite reason for doing anything since 1992 when my mother told me I couldn’t watch the Simpsons.

The why of Power Pivot, on the other hand, is different — it’s not abstract. For the Excel user it’s the experienced pain of 1) not being able to refresh data, 2) writing VLOOKUP or INDEX/MATCH over and over again, 3) not being able to deal with large datasets, 4) making reports that are very difficult to maintain/edit, 5) struggling to create ratios that aggregate well, 6) dealing with data from multiple data sources, on and on. There are lots of constraints in old Excel and when you show Excel people the solutions in Power Pivot you can see the “why” on their faces. People light up. Lightbulbs go off. And when people understand Power Pivot they’re ready to do whatever it takes to get that raw data into the Power Pivot data model.

start with why

So starting with Power Query, before any Power Pivot, means starting withouta tangible why for most Excel users. But there’s a middle way — teach as much Power Pivot as needed to first establish the why. That may be as much as showing somebody a series of pivot tables with slicers and sparklines that get them excited. For others it may be showing them some time intelligence function like “YoY % Change in Net Income”. It may be showing them Power BI on a phone. For many it may be more in-depth than that. Either way, the best learning happens when people are motivated and so my first motion as a trainer is always to establish the why.

Just as a reference point — we start all of our training off with this slide …

Austin is VP of Operations & Growth at PowerPivotPro — check us out http://www.powerpivotpro.com

it’s real simple, what matters are results

I’m in Chicago today to work with one of my favorite clients. I love working with them because they are focused on the right things. They’re focused on business results and their strategy for getting results is to encourage their people to follow the right process when trying to turn data into actionable insight. We’ve helped them shape that process and here’s what it looks like:

  1. Raw Data
  2. Data Transformation, Cleaning
  3. Data Modeling
  4. Writing Calculations, Analytics
  5. Interactive, Actionable Reporting

This process is sort of happening in Old Excel right now for tens of millions of people but it’s happening in a way that’s incredibly time consuming and manual, in a way that requires a lot of work and even more re-work. The most important part of this process, the way most of us want it to be, is that the process is automated — meaning we build something once and then hit refresh the next time we need to update it. That process is mostly not happening in Old Excel right now but it’s definitely happening for everyone who’s made the leap to Modern Excel with Power Query and Power Pivot. It’s also happening for everyone that’s made the jump to other BI platforms like Alteryx or Tableau.

So here’s the inspiration for me writing this. This client is communicating this to their people:

Look we’re going to give you Tableau, Alteryx, Power Pivot / Power Query / Power BI and we don’t care which one you use; we only care about you following this process.

Wow. You might think this would make me a little uneasy — I mean at PowerPivotPro we certainly have a bias around which tools are the best, but this approach actually makes me very happy. It’s real simple, what matters is results, and the way you get results with data is by following this process.

If you’re caught up in this nonsense about which tool is the best and that’s keeping you from the bigger picture then I feel bad for you. You can make absolute shit with any analytics product on the market; you can also make pure fucking gold. The distinction isn’t between people that are using this tool or that tool. In the world of analysts the distinction is between people that are delivering actionable insights that move the needle on their business and those people that are delivering noise. If you’re not aware of this distinction then you’re probably on the wrong side of the equation. Get with it!

So here’s my question to you — out of all the steps in the process that I layed out which ones do you find the most interesting or puzzling. Drop me a comment and let me know and I’ll write my next post about whatever gets the most interest.

Austin is VP of Operations & Growth at PowerPivotPro – check us out http://www.powerpivotpro.com

My Power Pivot Story

I’ve worked through a major life inflection over the last five years — going from using Excel the old way to using Excel the new way, from Old Excel to Modern Excel. Now I’m going through another transition — I’m working full time at PowerPivotPro to help other Excel people make this same transition. I want to share a little of my story because you may see some of yourself in it.


Sometime in the middle of 2012 Tom Osterbind leaned over the cubicle wall and asked me if I had heard of Power Pivot.


I started off my career as an analyst — living through 5 cubicle years of analyst work from 2011 to 2015 — armed with Excel, crunching numbers, developing information, helping decision makers bring data into decisions. In 2011, working at a large global bank, this work felt amazing because I was great at it. People respected my work; they respected the work. They feared my mighty VLOOKUP, INDEX/MATCH, & Pivot Table skills. We produced things relatively quickly that were accurate, good looking, and most importantly useful. I was an Excel Pro basking in the glory but not for long.

As everyone who’s worked with me knows I’m naturally uncomfortable with complacency, especially in the skills game. What I know now may be worth a lot but damn look at this better way of doing things! Just a year into my Excel Pro life I found myself writing the same Excel formulas over and over again, copying and pasting data in a thousand ways to make a chart, doing as much re-work as actual work, and not doing nearly enough thinking. I was really, really good at it but there wasn’t much satisfaction in the work. After all I was trained to solve complex problems in accounting and finance.

At this very moment there are tens of millions of people that power the global economy with Excel who are experiencing this same dilemma.

These folks didn’t enter the workforce as Excel people. They are accountants, financial analysts, supply chain people, marketers, and many others. They are senior executives and entry level analysts who are interested in moving the needles for their organization. I’m one of these people. We show up in the morning, open Excel, and get to work trying to turn our organization’s data into information into reports into decisions into business results. It’s an exhilarating proposition but the work itself can be a real slog. It was for me, until sometime in the middle of 2012 when Tom Osterbind leaned over the cubicle wall and asked me if I had heard of Power Pivot.


I wanted in to the party and I started knocking on the door.


Tom was my cubicle mate then and is still a good friend today. We were trying to a solve a problem that seems pretty simple on its face. We were trying to make a report that would produce financial ratios for different cuts of data — customers, regions, products, etc. The ratios had to compute correctly at every detailed level of the report as well as in the subtotals and totals. Most Excel users familiar with this type of problem would tell you that the only solution requires calling your spouse and telling him/her that they can go ahead and eat dinner without you, you’re working late. It’s a bullshit, non-solution solution — doing this kind of basic thing in Excel just didn’t work, and if you do get the result together god help you if a manager asks you to change something about the report — ”Cool you broke it out by product category, would you mind doing the same thing but by product subcategory?” “NO, NO, NO!” … that’s what you’re thinking. “Sure, no problem” … that’s what you actually say and then get back on the phone with your spouse.

Of course there are lots of solutions to this problem but the one we stumbled on in 2012 was Power Pivot. Microsoft had quietly released this add-in for Excel 2010 that allowed all sorts of magic to happen and we downloaded a copy and started exploring. Here’s what we discovered …

Excel people, my people, listen up. 90% of the drudge work that you do on a daily basis in Excel is not necessary anymore, and the solution to all that mind numbing work is right in front of you, in Excel.

A few months later in the fall of 2012 I took a job in a sales and marketing department where I got to do Power Pivot all the time and damn I started having fun. People would bring me their reports that we’re taking three days to complete every month — super manual work. We’d transform that spreadsheet into a report that would update automatically with the click of a button producing more accurate information. We were doing all of this with Excel, but not plain old Excel, Excel with Power Pivot.

As a result of a few early wins I started doing this work for people all over the company. Officially I was “Marketing Coordinator”, unofficially I was “Lead Power Pivot Evangelist”. There’s one universal thing that happens when a person catches the Power Pivot bug — they won’t the shut the fuck up about how awesome it is. I was this person. “Oh I see you’re using Excel, might I share with you the life-changing magic of Power Pivot? Oh you’re not interested? (grabs mouse) Here let me just show you this real quick.”

In the three years at that job I built some amazing things and inspired a couple of people to join me on the Power Pivot journey (cheers to you Joey Azar) but at no point did I feel like I was doing enough. My Excel people needed to hear about this. There were a few people that had carved out a space on the internet to share this story — Rob Collie & Avi Singh — and I was very drawn to their message. I wanted in to the party and I started knocking on the door.


Everyone who has experienced the drudge work of doing things the old way is ready for the new way, even if they haven’t articulated that yet.


In 2013 I had the good fortune of meeting Rob Collie at a conference and hearing him speak. Rob might not remember this but I asked him what it would take for him to mentor me and he basically said “you can’t afford me.” He didn’t mean it in a mean way and I didn’t take it personally. Three years later I’m on the PowerPivotPro team and he’s mentoring me and he’s paying me so HA, take that Rob. 🙂

I started working at PowerPivotPro in the fall of 2015. I’m on the field trip now — seeing dozens of client situations every month, learning what works and what doesn’t in training and consulting. I’ve gone from being a Power Pivot enthusiast to being a real expert. An expert in my mind is someone who’s made all the important mistakes in a field and lived to share those stories with others, and that’s basically what we’re up to at PowerPivotPro. We take the sum of our team’s experience and help individuals and organizations get further along on their Power Pivot journey. Pretty cool, right?

Here’s my message to you, my Excel people, if you’re an Excel person reading this and haven’t started working with Power Pivot you’re not alone.

My guess is out of the 500M people that use Excel worldwide, only about 5M of you are using Power Pivot (that’s a measly 1%). Getting this number from 1% to 5% would bring an enormous amount of value to businesses around the globe.

We want to bring this experience to Excel people. Notice I didn’t say we want to bring people this tool to people. We start all of our trainings off with a discussion about emotions — it’s really touchy-feely stuff because learning new things in life is not fundamentally about your talent, it’s mostly about your motivation. And everyone who has experienced the drudge work of doing things the old way is ready for the new way, even if they haven’t articulated that yet. We start with the motivation, the organizational decisions that you need to make to move the needle, and then bring in the tools.

Looking forward to sharing lots of Power Pivot over the next few years. For now though I’m headed out to the beach …

Austin is VP of Operations & Growth at PowerPivotPro (http://www.powerpivotpro.com)

1-G-KWhl8lwYmS8ymRBiqTkw