Linking Tables with One-To-Many Joins - Practice

Quick reference

• 00:04 What we're gonna do now is create some relationships so
• 00:07 that we can actually start using some of these fields together in one pivot table,
• 00:12 even though they come from different sources.
• 00:15 So here's what we're gonna do.
• 00:16 We're gonna jump into Power Pivot, because we need to link these tables together so
• 00:21 that we don't get that yellow error message.
• 00:24 We're gonna go over to Diagram View, and
• 00:26 we're gonna take a look at what we actually have here.
• 00:29 So, I've got a Transactions table here that has a whole bunch of
• 00:32 information on it.
• 00:33 I've got a Budgets table here that has a bunch of other fields on it as well.
• 00:37 And then I've got my Departments table.
• 00:39 I'm just gonna move this guy down here for right now.
• 00:42 Now, remember, in my Departments table, if I go back and look at my Data View,
• 00:47 I have a one nice unique relationship here, there's no repeating duplicates.
• 00:53 On my Budget's table, I have the same department number many, many times.
• 00:57 And on my transactions table, again, the same thing,
• 01:00 same, but department number many, many times.
• 01:03 So I'll hop back into Diagram View, and
• 01:06 what I'm gonna do is I'm gonna drag from department to department.
• 01:10 And I'm gonna let go.
• 01:12 And at this point, it will create the relationship between the two.
• 01:15 And if I click on the arrow, I can see which fields are related.
• 01:18 Now, I guess it before immediately when you do this, you should right
• 01:23 click the one on the many side, so that's got the star in Excel 2016, or
• 01:27 the dot in Excel 2013, right-click, and choose Hide From Client Tools.
• 01:33 And this will prevent anyone from ever using this field on a fact table.
• 01:38 Remember, we only want facts on the fact table, not dimensions.
• 01:42 I'm also gonna do the same thing,
• 01:44 Department is gonna go over to the Budgets table.
• 01:47 Again, because this is the many side of the relationship, we'll right-click and
• 01:51 we'll Hide from Client Tools.
• 01:55 Now, I'm just gonna go back to Excel.
• 01:58 And at this point, what you'll see is we have a slightly different experience.
• 02:03 When I grab a department name and put it onto columns,
• 02:05 you'll notice that everything slices up quite nicely.
• 02:08 So that actually looks pretty darn good, I'm pretty happy with that.
• 02:12 Now, at this point I'm gonna take this off for a second though, because I want to see
• 02:17 what happens if I go and now throw on something like, maybe, Budgets and Amount.
• 02:25 You'll notice again, we get relationships between the tables may be needed, so
• 02:28 that's not really gonna help us all that much.
• 02:30 We definitely don't wanna have that happen.
• 02:33 And yet, watch what happens when I do something like this.
• 02:37 Let's put the amount back on for a second.
• 02:40 So, this is gonna be my Budget dollars.
• 02:46 So I click over here, here we are, here's Budget dollars.
• 02:50 And for my transactions, I'm gonna go and call this one, sales dollars.
• 02:53 We can see that Budgets are not slicing.
• 02:56 These guys are all the same as they used to be.
• 02:59 And what I'm gonna do now is I'm gonna actually remove group.
• 03:02 And I'm gonna remove Class from the Pivot table.
• 03:05 And you'll notice the yellow message goes away and it says, hey,
• 03:07 I've got these two values.
• 03:09 And I can grab department name and pull it onto rows, and these guys work.
• 03:13 You think, well what is going on with that?
• 03:16 How come these guys work, and everything cross filters correctly, but
• 03:20 the last ones didn't?
• 03:22 So let's try some stuff here.
• 03:23 We go to Analyze, we can insert a Slicer.
• 03:26 Put my department name on here.
• 03:29 I'm just gonna put my Slicer over here on the left hand side.
• 03:31 And we'll shorten it down.
• 03:32 And now, I'm gonna go back.
• 03:34 And I'm going to remove department name from my Pivot table.
• 03:38 And I'm gonna go back to what I was doing.
• 03:40 Class, and boom, relationship between the tables may be needed.
• 03:46 Now, let's go take a look at something really quickly here, to manage.
• 03:51 The relationship I have is between Departments and Transactions,
• 03:56 and the Department's table and the Budget's table.
• 04:02 The dimension fields, department and department, were hidden.
• 04:08 And therefore, I'm forcing myself to use the dimension from the dimension table.
• 04:11 Because this can push its relationship down through these arrows into and
• 04:15 filter out the information from these tables.
• 04:18 And yet, when I go back, this field here, Class.
• 04:23 Class is a dimension because I'm using it on a row
• 04:27 which is coming from the Transactions table.
• 04:30 What else comes from the Transactions table?
• 04:32 My sales dollars, my amounts, my fact.
• 04:35 Because I'm picking up Class from my dimension table.
• 04:39 What's happening is that that Class cannot filter
• 04:42 back across to the Budgets table and filter the other items.
• 04:46 So, Class here has nothing from which to reach over here.
• 04:50 It can't send something up the one way street, it doesn't work.
• 04:53 It basically causes a filtering problem, and that's why budget never,
• 04:58 ever changes here.
• 04:59 So, in order to deal with this,
• 05:00 I'm gonna have to do something a little bit more drastic.
• 05:03 The challenge that I have, though,
• 05:06 is that I need to link my accounts and departments.
• 05:11 And actually, as a combination against these things, and I can't do that,
• 05:15 because if I try and actually go and link account to account, it tells me it can't
• 05:19 be created because each of these columns contains duplicate values.
• 05:23 So obviously, we still have something to slice through,
• 05:26 even though we're getting a little bit closer.
• 05:28 So what I'm gonna do right now, is I'm gonna pull Class off of this Pivot table.
• 05:32 I've got my Sales and my Budget, I can drill into them and filter and
• 05:36 see what's going on with them.
• 05:37 So that's good, but
• 05:39 obviously there's still more work here that needs to be done.

Lesson notes are only available for subscribers.