The Need for Power Pivot

Quick reference

• 00:04 So why do we need Power Pivot?
• 00:06 What's the big deal with this anyway?
• 00:08 Well let's start by taking a look at the Pivot Table Problem.
• 00:11 You've got two tables, you have a table of transactions and
• 00:15 you have a chart of accounts.
• 00:17 And now, you wanna show your amounts with the account names in a pivot table.
• 00:21 So what do you do?
• 00:23 And the answer to this one for anybody that's worked with pivot tables for
• 00:26 a long time, is actually pretty obvious.
• 00:27 Well, that's easy, you just go and you VLOOKUP the two together so
• 00:30 that you can take your transactions.
• 00:32 VLOOKUP the charter accounts to get the names back, and
• 00:35 we go through the process of what we call flattening our data table.
• 00:38 Because a pivot table, naturally, can only work from one table's source.
• 00:45 But now we get into bigger issues.
• 00:47 What if you want to add budgets?
• 00:49 So now you've got three tables, Transactions, Chart of Accounts, and
• 00:52 Budgets.
• 00:53 Which way do you VLOOKUP in order to flatten these guys into one big table?
• 00:57 The challenges that we run into, there's a maximum of one budget record per day,
• 01:02 typically on the last day of the month, but
• 01:04 there's multiple transactions posted per day.
• 01:07 So if you do a VLOOKUP from the transactions table,
• 01:11 you're only gonna pick up the budgets on the very last day of the month.
• 01:14 There might not even be a transaction for that day, or there could be six.
• 01:17 So you could get somewhere between zero and six different versions
• 01:21 of budget coming across which is gonna be a bit problematic.
• 01:25 What about accounts where there are no budgets or there are no transactions?
• 01:31 In either one of those cases, that's gonna cause you some problems as well
• 01:35 because there's gonna be gaps in your data.
• 01:37 Do you start with a chart of accounts and look at transactions?
• 01:41 Well, you would hope in this case that there would be an account for
• 01:44 every single transaction that's been posted.
• 01:46 But if there's multiple transactions per day and
• 01:48 there's only one record in the chart of accounts, that's not gonna help you.
• 01:51 Likewise, if you look at budgets.
• 01:53 So there's a really, really big challenge here.
• 01:55 Without aggregating and losing daily detail,
• 01:57 it is impossible to actually solve this and get this into a regular pivot table.
• 02:01 This is the unpivotable problem, and this is why we need Power Pivot.
• 02:08 Basic PivotTables at some point, will let us down.
• 02:11 Some of the tables are too complex to flatten for a regular PivotTable to use.
• 02:15 There's a calculated field language inside a basic PivotTable, but
• 02:19 unfortunately, it's very limited.
• 02:22 It even limits the kinds of aggregations we can use.
• 02:24 If you've ever looked for something like a distinct count in a regular pivot table,
• 02:28 you'll know what I'm talking about.
• 02:30 What Power Pivot adds for you is it adds the ability to link multiple tables and
• 02:34 serve one PivotTable all from this multiple table source, instead
• 02:38 of having to go and actually flatten everything down into one table first.
• 02:43 It adds a robust language to build complex pivot table formula.
• 02:47 Now this language is so
• 02:48 robust that we're gonna touch on it in this course, but there is a huge amount of
• 02:52 time that you could actually spend here learning different things.
• 02:54 It's like Excel formulas,
• 02:55 there are hundreds of these things that you can actually work with.
• 02:59 So I'll get you introduced to some of them inside this course.
• 03:02 What I will tell you is that language will give you the ability to
• 03:05 build virtually any aggregation need.
• 03:07 You want a distinct count, that's easy.
• 03:09 Do you need a standard deviation to go across your table, no problem,
• 03:12 we can make that happen.
• 03:14 All of these things are possible using the docs formal language.
• 03:18 This gives us the ability to build refreshable,
• 03:20 custom business intelligence for our solutions, stuff that we
• 03:24 couldn't necessarily do when we're working with just standard pivot tables.

Lesson notes are only available for subscribers.