PowerPivot for Excel - Lookup Table in Data Warehouse

Businessman using laptop in office meeting
Ariel Skelley/Blend Images/Getty Images

One of the things I list most about PowerPivot for Excel is the ability to add lookup tables to your data sets. Most of the time, the data you are working with doesn't have every field you need for your analysis. For example, you might have a date field but need to group your data by quarter. You could write a formula, but it's easier to create a simple lookup table within the PowerPivot environment. You can also use this lookup table for another grouping such as month name and first/second half of the year. In data warehousing terms, you are actually creating a date dimension table. In this article, I am going to give you a couple of example dimension tables to enhance your PowerPivot for Excel project.

Note: Versions this tutorial applies to are Microsoft Excel 2013-2016.

New Text Dimension (Lookup) Table

Let's consider a table with order data. Assume the table has fields for customer, order date, order total, ​and order type. We are going to focus on the order type field. Assume the order type field includes values like:

  • Netbooks
  • Desktops
  • Monitors
  • Projectors
  • Printers
  • Scanners
  • Digital Cameras
  • Digital SLR Cameras
  • Film Cameras
  • Camcorders
  • Office Phones
  • Smart phones
  • PDAs
  • Cell Phones Accessories

In reality, you would have codes for these but to keep this example simple, assume these are the actual values in the order table. Using PowerPivot for Excel, you would easily be able to group your orders by order type. What if you wanted a different grouping? For example, assume you need a category grouping like computers, cameras, and phones. The order table doesn't have a category field, but you can easily create it as a lookup table in PowerPivot for Excel. The complete sample lookup table is below in ​Table 1. To follow along in this demonstration, copy the text from Table 1 below. Here are the steps:

  • Step 1: You need a distinct list from the type field for your lookup table. This will be your lookup field. From your data set, create a distinct list of values from the order type field. Enter the distinct list of types into an Excel workbook. Label the column Type.
  • Step 2: In the column next to your lookup column (Type), add the new field you want to group on. In our example, add a column with a label called Category.
  • Step 3: For each value in your distinct list of values (Type, in this example), add the corresponding Category values. In our simple example, enter either Computers, Cameras or Phones into the Category column.
  • Step 4: Copy the Type column of data into your clipboard (Ctrl + C).
  • Step 5: Then click on the PowerPivot tab and select Manage.
  • Step 6: Within the PowerPivot window under Home, click Paste.
  • Step 7: Enter in a unique name in the Table Name box and make sure you check Use first row as column headers.
  • Step 8: Click OK. You have created a lookup table in PowerPivot.

<insert image 1>

  • Step 9: Repeat steps 4  -8 for the Category column making sure you name this table differently than the first one. 
  • Step 10: Create a relationship between the Type field in the Order table and the Category field in the lookup table. Click on the Design ribbon and choose Create Relationship. Make the selections in the Create Relationship dialog and click Create.

<insert image 2>

When you create a PivotTable in Excel based on the PowerPivot data, you will be able to group by your new Category field. Keep in mind that PowerPivot for Excel only supports Inner Joins. If you have an order type missing from your lookup table, all of the corresponding records for that type will be missing from any PivotTable based on the PowerPivot data. You will need to check this from time to time.

Date Dimension (Lookup) Table

The Date lookup table will most likely be needed in most of your PowerPivot for Excel projects. Most data sets have some type of date field(s). There are functions to calculate the year and month. However, if you need the actual month text or the quarter, you need to write a complex formula. It's much easier to include a Date dimension (lookup) table and match it up with the month number in your main data set. You will need to add a column to your order table to represent the month number from the order date field. The DAX formula for month in our example is =MONTH([Order Date]). This will return a number between 1 and 12 for each record. Our dimension table will provide alternate values, which link to the month number. This will provide you flexibility in your analysis. The complete sample date dimension table is below in ​Table 2.

The date dimension or lookup table will include 12 records. The month column will have the values 1 - 12. Other columns will include abbreviated month text, full month text, quarter, etc. To follow along in this demonstration, copy the text from Table 2 below. Here are the steps:

  • Step 1: Copy the table from Table 2 below and paste into PowerPivot. You could create this table in Excel but I am saving you time. You should be able to paste directly from the selected data below if you are using Internet Explorer, Edge, or Google Chrome. PowerPivot picks up the table formatting in my testing. If you are using another browser, you may have to paste into Excel first and copy it from Excel to pick up the table formatting.
  • Step 2: Copy the data from Table 2 into your clipboard (Ctrl + C).
  • Step 3: Then click on the PowerPivot tab and select Manage.
  • Step 4: Within the PowerPivot window under Home, click Paste.
  • Step 5: Enter in a unique name in the Table Name box and make sure you check Use first row as column headers.
  • Step 6: Click OK. You have created a date lookup table in PowerPivot.

Now you’ll need to create a simple table for the second part of this tutorial. In Excel, create a 2-column table with the first column containing numbers representing the number of orders made in a month. The next column will contain numbers representing the months the orders were made. Example, 1 = January.

  • Step 7:Repeat steps 2 - 6 for the second table.
  • Step 8: Create a relationship between the Month field in the Order table and the MonthNumber field in the lookup table. Click on the Design ribbon and choose Create Relationship. Make the selections in the Create Relationship dialog and click Create.
  • <insert image 3>

Again, with the addition of a date dimension, you will be able to group the data in your PivotTable using any of the different values from the date lookup table. Grouping by quarter or the name of the month will be a snap.

Sample Dimension (Lookup) Tables

Table 1

Type Category
Netbooks Computer
Desktops Computer
Monitors Computer
Projectors & Screens Computer
Printers, Scanners & Fax Computer
Computer Setup & Service Computer
Computers Accessories Computer
Digital Cameras Camera
Digital SLR Cameras Camera
Film Cameras Camera
Camcorders Camera
Cameras & Camcorders Accessories Camera
Home & Office Phones Phone
Touch Screen Phones Phone
Smart phones & PDAs Phone

Table 2

MonthNumber MonthTextShort MonthTextFull Quarter Semester
1 Jan January Q1 H1
2 Feb February Q1 H1
3 Mar March Q1 H1
4 Apr April Q2 H1
5 May May Q2 H1
6 Jun June Q2 H1
7 Jul July Q3 H2
8 Aug August Q3 H2
9 Sep September Q3 H2
10 Oct October Q4 H2
11 Nov November Q4 H2
12 Dec December Q4 H2