Microsoft Office Tutorials and References
In Depth Information
The only missing point is a relationship between the SalesOrderHeader and this new table.
To create the relationship, you need to go to the SalesOrderHeader table, choose the column
OnlineOrderFlag, and click the Create Relationship button on the Design tab of the ribbon. This
action opens a dialog box in which you describe the relationship. It should look like Figure 2-20.
FIguRE 2-20 Definition of the relationship with the decoding table in PowerPivot.
In Figure 2-20, you are stating that the OnlineOrderFlag in the SalesOrderHeader table is
related to the column OnlineOrderFlag in the SalesOrderHeader_OnlineOrderFlag table.
Because the related columns have the same type (a TRUE/FALSE value), the relationship
can be created. Clicking Create is enough to make PowerPivot analyze data and create the
This kind of table, which contains keys and values describing them, is normally called a
Lookup Table because it allows you to give a name to a code by looking up the code in
Lookup tables are very similar to the Excel VLOOKUP functions. If, in a standard Excel
worksheet, we want to provide a description to a particular code, we might use a
VLOOKUP function in a cell that refers to a decoding area. Lookup relationships work
much the same way even if, with PowerPivot, we use relationships to create much more
Now that you have completed the creation of a linked table and defined the relationship with
this new table, it is time to return to the PivotTable and click the Refresh button to see what
has changed (see Figure 2-21).