digest
Welcome to PowerPivot video number three. If you want to download this file, "PowerPivot 301 - Replace VLOOKUP with Excel 2013 Data Model and Relationships," click on the link below the video. Now, in this video I'm actually going to show you a trick that I don't think I would ever really use, but some people might find it useful. The trick we're going to learn is how to get both of these tables into the PivotTable Field List so we can pull fields from both of them and build our report. Our end result here is just: I want region by revenue. Now, as I showed you in video 1, I would have an extra helper column and use VLOOKUP. It would be totally simple: look up the sales rep in this table over here, bring the region over, and have one table in the PivotTable Field List. But I'm going to show you this trick because it's pretty cool, and guess what, it's not going to even require that we have Power Pivot. As long as you have Excel 2013, we can indirectly access the Data Model and relationships.
All right, here's what I'm going to do. Before we do that, both tables have to be Excel tables, so I'm going to convert this to a table: Ctrl+T, Enter. Instead of going to Design and up here to name the table, I'm going to press Alt, J, T, A, which jumps me up here, and I'm going to call this "D_SalesRep," Enter. Now we have two Excel tables. If we build a PivotTable from either one of these, both tables will show up in the field list. So let's just start with this one right here. Look, no Power Pivot. Where do we go to get PivotTables? Insert PivotTable, or we can use the keyboard, Alt+V. It selected the right table. We're going to put it on a new worksheet, but check this out: add this data to the Data Model. Guess what: when you buy Excel 2013, it has that columnar database, the xVelocity engine, already in it. You may not have the Power Pivot ribbon tab to do a lot of the cool things, and we can't build calculated columns and calculated fields because we don't have that Power Pivot ribbon, but we can build relationships and have two tables in our field list. I'm going to click OK. This will bring us to a new sheet, and check this out: I can go over in the PivotTable Field List to All, and there are both tables now. One of them has a little black line and the other one doesn't. That means this one's been added to the Data Model and this one hasn't. But let's check this out. Let's try to pull a field, Region, down to the Rows area. Oh well, looks like it works, but when we try to bring Net Revenue down to Values, it's not going to understand that there's a relationship, and it's as if we get the sum of all the revenue in all the cells. Look at that. It says "Create Relationship." We can do this right from the PivotTable Field List. I'm going to click Create, and here's our table. This is the transaction table, so I'm going to pick the transaction table, and the column, as a foreign key, is where we would actually look up the lookup value from the transaction table. That will come from the Sales Rep column. Now look at this. If you watched video 1 in this series, when we did Create Relationships from the dialog box in Power Pivot, it says "Related Lookup Table." Really, this is where they need the lookup table. I don't know why they left it out here, but we're going to have to select the lookup table, which is Sales Rep, and sure enough, it found Sales Rep. This is the lookup table. This is the transaction table, the table where we would have our helper column in Excel.
All right, so now I'm going to click OK, and you're not going to believe this: both of these have been added to the Data Model, and our relationship is working. Without Power Pivot, we have pulled fields from two tables and gotten our report, all without VLOOKUP. Now, I would still probably just go ahead and use VLOOKUP, but I don't know, this is pretty cool. Nope, our PivotTable can still access the Data Model.
I'd like to go over to a different workbook and show you the same scenario, but actually with Power Pivot. So here's our second workbook. You can download "PowerPivot 301 - Replace VLOOKUP with Power Pivot and Relationships." Notice both tables, and I don't have my Power Pivot. If you have Power Pivot, you have the right version. You go down to Options, Add-Ins, down to Manage, and you're going to go to COM Add-ins and then click Go, and it'll be there. If you have the right version, click OK, and there's the Power Pivot ribbon tab. Now, if I were to go over and look right now, this is that second workbook. There can be only one Data Model in each workbook, and there'd be nothing there, but I'm going to click in a single cell, go to Power Pivot, and say Add to Data Model. Boom, it got our table, and there's the linked table name. There's our table. Alt+Tab to get back over here. Click here. The keyboard shortcut is Alt, B, Y to add to the Data Model, and sure enough, we have our two tables. Now we can come up to Diagram View. We did this in video 1, but just in case, let's check this out. We have our two tables. Here's our lookup table, or dimension table, and there's our transaction, or fact, table. I'm simply going to drag Sales Rep over to Sales Rep, and boom, that's how we could create a relationship in Power Pivot if we wanted to. Right-click, Edit Relationship, and we can see the difference in the dialog box: table, column, related lookup table, related lookup column. Why didn't they have that over there when we don't have Power Pivot? I don't know why they didn't do that. Click OK.
Now, if we wanted to create our PivotTable, we simply click it, it's going to insert it into a new sheet, and I'm going to click OK. We have our two tables, and they already have the dark line. I'm going to click Region for rows, and Transaction Net Revenue for values. All right. In this video we saw that yes, if you have Power Pivot, we can go ahead and add our Excel tables into the Data Model, build a relationship, and then build reports from multiple tables. If we didn't have Power Pivot, we could still build relationships, as long as our two tables were actual tables. We could add them to our Data Model and build relationships using the PivotTable Field List.
All right, next video we're going to look at an array formula and compare it to a function in Power Pivot, and then the following video will finally start a comprehensive example, importing from multiple sources and doing lots of calculated columns and calculated fields. All right, we'll see you in the next video.