In this video, the instructor introduces Power Pivot and outlines some of the key benefits.
- [Instructor] Okay, so we've mastered power query, we've gone through the fundamentals of data modeling 101, now it's time to officially meet Power Pivot. Now, I'm gonna let you in on a little secret, which is that a Power Pivot is just like a normal PivotTable except that it sits on top of an entire data model instead of a single table or range. So this is one of those naming conventions things that's a little bit tricky to wrap your head around at first, but there's no separate tool called a Power Pivot compared to a PivotTable.
We'll be using the same familiar PivotTable tools but the big difference that makes it a Power Pivot, is that it's connected to a data model. So it might sound like a kind of minor difference at first, but this actually enables some pretty incredible benefits. So number one, you can explore massive datasets, so datasets that are too big to fit in a worksheet and have to be compressed into the data model. And two, you can analyze multiple sources and tables in a way that you never could before.
So that was always the biggest limitation for me, working with PivotTables, is that any time I wanted any sort of more complex, blended view of performance that incorporated multiple sources of data, I would have to stitch and blend and join that data together in tables or worksheet cell ranges just so that I could point the PivotTable to that one source and analyze everything together. With Power Pivot, that's completely out the window. Now we move that whole process of joining and blending data outside of the worksheet and into the data model, where we have much more efficient, much more powerful and flexible tools for doing that, and in the beauty of it, is that we can insert a pivot and use the same familiar, user-friendly PivotTable tools and options to explore it.
So that alone is absolutely game-changing and worth ramping up on these tools in itself. A second difference between a Power Pivot and a regular PivotTable, is the ability to create a new type of powerful and flexible calculation using DAX. So we're basically gonna take this concept of calculated fields that you may be familiar with if you're comfortable with pivots, and we're gonna add a huge amount of fire power behind that.
So, in your Power Pivot tab, you'll see a few tools to manage the data model, define new measures, refresh sources. Again you may need to enable this tab if you don't see it, just head to File, Options, Add-ins, Manage COM Add-ins. And really the only two buttons that we're gonna be focusing on in this entire tab, are the first two. And you already know the first one, that Manage data model button just takes you right to your data model window. The second one is Measures and that's where we're gonna write all sorts of DAX functions and expressions in the next section of the course.
So as far as creating or inserting a Power Pivot table is concerned, two easy ways to do it. Option one is straight from your data model. So in your Home tab, you'll see this button that says PivotTable, and that just allows you to drop a pivot on a new or existing tab, and you'll see a field list just like this. So, should look pretty familiar, got your fields, your filters box, your columns, rows, and values. There are some differences which we're gonna talk about in the next video. Now option two, is from the regular old Insert, PivotTable dialog box.
The only difference is that now we're gonna check that box that says, Use the workbook's Data Model. Press OK, and you end up in the exact same place. So let's pop into our data model and actually insert our first Power Pivot table. Okay, so here I am, I'm in the Data View of my FoodMart Data Model workbook, let's go ahead and start here and in the Home tab, let's see right here I've got my PivotTable option. If you drill down, it lets you insert other types of objects like PivotCharts, Tables and Charts, Flattened Pivots, really just gonna focus on PivotTable for this course.
So press PivotTable, it says alright you wanna drop that into a New Worksheet or an Existing Worksheet? Let's say, put it into an Existing one, in cell A1, press OK and there you have it, and close my queries box, and there's my field list which should look very familiar at this point. That's option number one, let's go ahead and take a look at the second option from here in my Worksheet View, and it's as simple as going to Insert, so I need to deselect that pivot first, Insert, PivotTable, Use this workbook's Data Model, that's the key, whereas all the pivots you've been used to using up to this point, have selected a table or range as the source.
So why don't we put this one into a New worksheet, and press OK and there you go, PivotTable two in Sheet two, the two are essentially identical, and there's our field list, so there you have it, we've got our first PivotTable. In the next few lectures we're gonna drill into our data model and start exploring the data we have to work with. So let's just go ahead and delete the duplicate on Sheet two and we're left with just our one, and we are good to go, so stay tuned for more.
- Power Pivots versus normal pivots
- Creating calculated columns and measures
- Power Pivot and DAX best practices
- Math, stats, and COUNT functions
- CALCULATE, FILTER, and ALL functions
- Joining data with RELATED
- Using iterator functions (SUMX and RANKX)
- Time intelligence formulas