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