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.
Download this lesson’s related exercise files.Linking Tables with One-To-Many Joins - Practice.xlsx
677.2 KB Linking Tables with One-To-Many Joins - Practice - Completed.xlsx
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.
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
- 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
Lesson notes are only available for subscribers.