This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Define Relationships Between Tables
PowerPivot has an automatic relationship detector. It works when you are linking from a base table to a lookup table and there is exactly one perfect matching column. It is cool when it works, but in real life, it is just as easy to explicitly define the relationship.
Your main Sales table has a Customer field. The Sector table also has a Customer field. Here is how to tell PowerPivot that these table should be linked:
1. Go to the Customer heading in the Sales table in the PowerPivot window. Right-click the heading and choose Create Relationship.
Figure 953 Start in the base table and right-click the field that should link to the lookup table.
2. By taking the time to start from the proper column in the base table, the first half of the Create Relationship dialog will be filled in for you. The top Table and Column will show the table and column that you clicked on.
3. Open the drop-down for Related Lookup Table and choose Sector. If the Sector table has a field that is also called Customer, this will automatically be filled in for the Related Lookup Column. Literally, it takes one selection in this dialog to define the relationship. Click Create.
Figure 954 Define a relationship between the two tables.
You would follow a similar process to link the Region field to the Bonus Rate table.