Normally, in regular Excel you would be creating VLOOKUPs to match the two tables. It is far easier in PowerPivot. Follow these steps:
- You will be linking from one column in your main table to a column in another table. To simplify the relationship process, navigate to your main table and select a cell in the column from which you will be linking.
- Click the Design tab in the PowerPivot Ribbon.
- Select Create Relationship. The Create Relationship dialog appears. By default, the selected table and column will appear in the first two fields, as shown in Figure 2.15.
Figure 2.15 Define a relationship between tables. By selecting the key column before starting, 2 of the 4 fields are populated.
- If you skipped step 1 and the correct table is not shown in the Table drop-down, then select Demo from the Table drop-down.
- If you did not select the correct column in step 1, open the Column drop-down. Select StoreID.
- Open the Related Lookup table drop-down. Select StoreInfo.
- Open the Related Lookup Column drop-down and select Store (see Figure 2.16).
Figure 2.16 This simple dialog replaces the VLOOKUP.
- Click Create. You've now created a relationship between the two tables.