Focus video player for keyboard shortcuts
Auto
- HD
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
In this lesson we will begin linking the tables in our Data Model and show you how it enriches the PivotTable experience.
Exercise files
Download this lesson’s related exercise files.
Linking Tables with One-To-Many Joins - Practice677.2 KB Linking Tables with One-To-Many Joins - Practice - Completed
702.5 KB
Quick reference
Linking Tables with One-To-Many Joins - Practice
An overview of creating a One to Many relationship.
When to use
When you are ready to link two tables in order to use fields from multiple data sources in your Power Pivot Table.
Instructions
The general process is:
- Open Power Pivot by going to Power Pivot --> Manage
- Go into the Diagram View and resize and/or move the boxes representing the tables as needed
- Click and drag a field from one table and drop it on top of the field in another table that you would like to link it to
- Right click the field on the Many side (the side with the *) and select Hide from Client Tools
Changing Relationships
- To delete a relationship, select the arrow and press Delete
- To modify a relationship, double click the arrow
Hints & tips
- When creating a One to Many relationship, it is impossible for Power Pivot to get the relationship backwards (you can drag from one to many or many to one and it will be correct)
- Hiding the field on the Fact table prevents users from using this field to a Dimension table
- Click on the arrow to highlight which fields are joined
- 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.