Preparing Source Data

Quick reference

• 00:04 In this video, we're going to look at preparing our source data properly
• 00:08 to feed it to a pivot table.
• 00:09 And the reason we need to do this is because when you go to a table like this
• 00:13 one here.
• 00:14 And we can see it's an official Excel table because it does come up with a table
• 00:17 tool design tab and it has a name.
• 00:19 Just like the orange table on the left here, which is inventory.
• 00:23 The challenge is when we go to something like our sales table, and
• 00:26 we go to insert pivot table.
• 00:28 You'll notice that it only allows us to choose one single table, and
• 00:31 that's a bit of a problem because in the data that I have here,
• 00:35 I have the sales quantity, but I don't have any price.
• 00:38 That exists in a different table all together.
• 00:42 In a standard Excel pivot table,
• 00:43 you could only source from one particular table of data.
• 00:46 So what does that mean?
• 00:48 It means we need to put these two tables together into one
• 00:51 in order to be able to feed it to a pivot table.
• 00:53 How do we do that?
• 00:55 You can use Power Query in order to be able to actually
• 00:57 merge two tables together.
• 00:59 Or the classic way of course, is to use something like V lookup.
• 01:03 So what I'm going to do is I'm going to come over to my blue table over here, and
• 01:06 I'm going to add a new column for my sales price.
• 01:10 Now when I do that, it expands the table automatically.
• 01:13 And the key thing that I need to do now is look up the appropriate sales price for
• 01:17 my SKU or stock keeping unit.
• 01:19 Why am I picking on this one?
• 01:21 Because there's many,
• 01:22 many repeating instances of the SKU number in my sales table.
• 01:26 But over in my inventory table, there is only one instance of the SKU number.
• 01:31 There's no duplicates here, so we can look it up and get the exact match for
• 01:35 the information that we want.
• 01:37 So to do that, what we'll do is we'll start with our nice little V-lookup
• 01:39 formula, it's equals V lookup.
• 01:42 It asks, what's the value we would like to lookup.
• 01:44 We're going to look up the SKU number so it shows us @SKU.
• 01:48 The next question is comma, where would you like to look it up?
• 01:52 And we're going to look it up inside the entire table of inventory.
• 01:57 Now, as it happens, we don't actually need this all part on here.
• 02:00 We could just get rid of that so
• 02:01 we'll just go with inventory, the inventory table.
• 02:04 And then of course,
• 02:05 we need to know which column do we want to return if we find a match for the skew.
• 02:10 So we're going to go one, two, three, four, five sales prices, column six.
• 02:15 So six, six, and then come a false because we don't want to guess on this one here.
• 02:20 We want an exact match or return a hash NA.
• 02:23 Now a nice thing with tables of course,
• 02:25 when I hit Enter, it runs the same formula all the way down.
• 02:28 And I could also go and say, hey, this is pretty cool, why don't I go and
• 02:31 get my cost?
• 02:35 Again, we'll do a nice little V-look up here.
• 02:38 We'll look up the SKU number.
• 02:40 We'll look it up in the inventory table.
• 02:44 This time we'll return column seven, comma false if there's no match, and
• 02:49 there we are.
• 02:50 And the final thing is, we'll also grab the margin.
• 02:54 Once again, I'll write my last little V-look up here,
• 02:57 we'll look up the SKU number.
• 02:59 And again, in the inventory table, and this time we'll return comma eight,
• 03:04 comma false to pull back the correct values.
• 03:08 Now that we have all that in place, I can go and build my pivot table.
• 03:12 So we'll go and we'll say Insert, Pivot Table,
• 03:14 and we'll put it on a new worksheet.
• 03:17 I'm quickly going to go and configure this so
• 03:19 that I can actually take a look at my brands on rows.
• 03:23 And then I can look at something like my sales price on values.
• 03:28 Now, the challenge that I've got here though is it's actually going and
• 03:31 it's summing the sales price entries that we actually have here.
• 03:35 And that's not really what I want.
• 03:37 What I actually want is the total sales because I want to have the number of
• 03:40 units.
• 03:41 We actually see that this really isn't working for us when we go back and
• 03:44 we pull in the sales quantity.
• 03:47 There we are, we've got 64 units, but we've got a sum of sales price here,
• 03:50 now Heineken 4350.
• 03:51 I don't think that's the right price for that one, but
• 03:53 it's because adding multiple rows for Heineken.
• 03:56 So how do I actually go and get the sales quantity times the sales price?
• 04:02 Well, to do that, the easiest way is to actually come back and
• 04:06 do it in the source table itself.
• 04:08 So what I'll do is I'll come back and
• 04:10 I'll say let's create a new column here called sales dollars.
• 04:13 And this is going to be simply sales quantity times the sales price.
• 04:20 I can now also come across and say, why don't we go and grab our cost dollars?
• 04:27 This one is going to be the sales quantity times the actual cost.
• 04:33 And then we could have something like total margin which is how much money we
• 04:37 made by saying sales quantity times the actual margin itself.
• 04:45 Do we need to do this in a source data?
• 04:47 No, we don't.
• 04:48 Later on we're going to show you how you can actually do this with formulas
• 04:50 inside the pivot table.
• 04:51 Sometimes this is actually a lot easier to do.
• 04:55 When I come back now, a nice little refresh in my pivot table.
• 04:58 You'll see that I've got my nice new fields here.
• 05:01 And now I can pull on sales dollars, which has given me the correct sales.
• 05:06 I can now put on my cost.
• 05:09 I can put on my margin, and then I can come back and say hey, you know what?
• 05:15 I don't actually need the sales price at all.
• 05:17 So now I've got all the values that I need, they're calculating correctly.
• 05:21 And it's easiest to do by actually manipulating the underlying source table
• 05:24 rather than trying to get into some other black magic techniques later.

Lesson notes are only available for subscribers.