Sign in or start a free trial to avail of this feature.
3. Creating New Tables and Relationships
In Power Pivot, new data tables must be imported into the data model. See how to do this in this lesson.
Creating new tables
- Tables of new data cannot be created directly in Power Pivot
- If you need to do this, the easiest option is to create the new data in Excel, then copy and paste it into the data model
- When you add a new table, remember to link it to the existing data model by creating a relationship
- Alt A, M – Remove duplicates
- Control + C – Copy
- Control + V – Paste
- Alt H, W Open Diagram View
In previous Power Pivot courses, we saw how to add data to the data model from various sources.
In this lesson, we'll revisit that topic and add a new table to our data model.
We're going to create a new table to store various details about each of the products that feature in the data set. The first column in the table will be a list of all the products in the data set. Unfortunately, we can't add a new table directly into the data model in Power Pivot.
We must import the data we want from an external source, such as Excel.
Therefore, we'll go to the Pharma Data table in excel and create a column containing the unique product names.
We'll select the Product Name column, copy it, and paste it to an empty column.
We'll remove duplicates with Alt, A, M.
This produces a column of unique product names.
We'll now select this new data and copy it.
We'll then return to the data model and paste the product names.
We can now see the Paste Preview window.
We'll call the new table, Product Details.
Leave the column name as headers box checked and select OK to paste.
We now have a new table with a single column containing all the products in the Pharma Data set.
Over the rest of this course, we'll add several columns to this table, giving various details about the products in our data set.
Before that, we'll integrate this table with the rest of the model, by creating a relationship. Let's go to diagram view with the shortcut Alt, H, W.
First, we'll move the tables around so that the Pharma Data table is in the middle.
Let's also expand the Pharma Data table to see all the columns.
We'll now create the relationship by dragging the Product Name column in the new Product Details table to the Product Name column in the Pharma Data table.
And with that, we've added a new table and linked it to our existing tables.
Unfortunately, we cannot add a table directly in Power Pivot. All new tables have to be created by importing data.
However, we can see that the process is easy enough for a small table like this.
In the next lesson, we'll start looking at some of the simplest and most common functions you'll use in DAX.