Creating a Power PivotTable

Quick reference

• 00:04 >> It's now time to create our first Power Pivot pivot table.
• 00:09 And what we're gonna do is we're going to go to the Summary tab and
• 00:12 we're gonna start building a financial report based on the three pieces of
• 00:16 information we have on the side.
• 00:18 The departments table, the budgets table and the transaction table.
• 00:22 Now, creating that Power Pivot pivot table gets easier in every single version.
• 00:28 I'm gonna show you how easy it is to start with Excel 2016.
• 00:34 We would just say insert and choose pivot table.
• 00:37 Starting from a blank cell, if we do that,
• 00:40 it automatically defaults to the data model, which is Power Pivot.
• 00:45 Note how it's not quite as easy in Excel 2013.
• 00:48 I find the easiest way to do it in Excel 2013, for reference,
• 00:52 is to actually come to the Power Pivot window and choose Manage.
• 00:56 And when you get inside here there's a button that has pivot table.
• 00:59 You can still do it through the regular user interface,
• 01:02 it just takes a lot more steps.
• 01:04 So in this case I can say pivot table and it'll ask me,
• 01:07 where would you like to put it?
• 01:08 New worksheet or existing, we'll say existing.
• 01:11 And I'm gonna put it right on top of this green cell B8 on the summary worksheet.
• 01:17 That will drop in a pivot table frame exactly like you're used to working with
• 01:22 a regular pivot table,
• 01:23 except that this one is actually linked back to Power Pivot.
• 01:27 So what can we do with this now?
• 01:28 Well, you know what, what we can do is we can say let's go right-click and
• 01:32 show the field list since mine's not showing.
• 01:35 And you're gonna notice, I'm just gonna get rid of the queries and
• 01:38 connections pane altogether here.
• 01:39 You're gonna notice that we actually have four objects that are showing in this
• 01:44 window if I'm looking at the All Fields.
• 01:46 Three of them look a little different than they use to.
• 01:49 They've got this little yellow bucket on the corner.
• 01:51 And they also have arrows to open them up.
• 01:54 And if I go and take a look at transactions, for example,
• 01:57 I'm gonna open this guy up and you'll notice that it's got all of
• 02:00 the fields that we've actually pulled in via Power Query.
• 02:03 As does departments, has department and department name,
• 02:07 and budgets has the four different fields that were available there as well.
• 02:12 Now something that I do wanna call out here is that in this interface you'll also
• 02:16 notice another department.
• 02:17 And this one looks like a regular old Excel table, and it is.
• 02:21 Personally my feeling is, if this thing is linked into Power Pivot, into the guy
• 02:25 with the little yellow bucket, it actually shouldn't show here at all.
• 02:29 But unfortunately, it still does.
• 02:31 These tables are dead to you, okay, we don't wanna use those at all.
• 02:34 What we're interested in is the ones with the yellow buckets.
• 02:38 Now what I'm gonna do is I'm gonna start to build a pivot table out of this.
• 02:42 I'm gonna go and I'm gonna grab Class, and I'm gonna drag it onto Rows.
• 02:46 And I'm going to grab Group from the Transactions table, and
• 02:49 drag it onto Rows as well.
• 02:50 So this looks pretty much the same.
• 02:53 I'm gonna grab Amount and drag it into the Values area as well.
• 02:57 You can see that it summarizes up quite nicely.
• 03:00 It works just like any pivot table would.
• 03:02 I can come back, I can grab Revenues, and I can even move it, left-click and
• 03:07 drag to snap this guy up here.
• 03:09 The secret if you didn't know for this one is your mouse pointer has to get to this
• 03:12 four-headed arrow in order to be able to left-click and drag, that's a manual sort.
• 03:16 It works the same as it does in any pivot table, no big deal.
• 03:20 What we really want though is we want the ability to be able to slice and
• 03:24 dice this by other things, like this for example.
• 03:28 If I grab Department and put it on Columns,
• 03:30 it slices out quite nicely to show the individual department columns.
• 03:35 Wouldn't it be cool though If we could actually grab it,
• 03:38 from a completely different table?
• 03:40 If we could grab Departments here, and say, let's use Department Name.
• 03:45 And when I do this,
• 03:46 you're gonna notice that something very seriously wrong happens.
• 03:51 It pops up a yellow message,
• 03:52 it says relationships between the tables may be needed.
• 03:56 There's an Auto Detect, and a Create.
• 03:58 Now I'm gonna advise you not to use auto detect because that's the purpose of this
• 04:02 course, is to teach you why you're getting this and what to do about it.
• 04:06 You'll also notice that when I actually look at this in the pivot table,
• 04:10 it's giving me the exact same numbers for
• 04:13 every individual column, for every individual row.
• 04:16 This obviously has a very serious problem here.
• 04:19 And the reality is, at the end of the day, the challenge,
• 04:23 we haven't actually told it which tables need to be VLOOKUP'd against each
• 04:28 other in order to actually get this information right.
• 04:31 So if you see this yellow message, it's a good idea to come back and
• 04:34 say you know what, something isn't quite right here, I better fix it.
• 04:38 Let me just pull this off for right now,
• 04:40 get back to a state where there is no yellow.
• 04:43 And now we need to learn a little bit about to how to actually perform
• 04:47 the VLOOKUP magic that you normally would use, although for Power Pivot,
• 04:51 we're never ever gonna use another VLOOKUP again.
• 04:55 So let's go take a look at what's required in order to make that happen.

Lesson notes are only available for subscribers.