24.4 Importing Additional Data

In this section, we will import additional data into our data model. We’ll first import data from a CSV file. Within Power Pivot, select From Other Sources, scroll to the bottom of the list of data sources, and select the Text File option.

In the dialog that follows, select the Employees.csv file you downloaded earlier. Also, make sure that you check the option to use the first row as column headers.

When you click Finish, it will import approximately 1,800 records and create a new table in the data model. You can rename the table and adjust any column names you want, as shown in the accompanying video. We will clean up some extra characters in the Employee_ID column name before going on by double-clicking on it and renaming it to Employee_ID without those extra characters.

If you switch to the Diagram View, you will notice that the data or table isn’t connected to any others yet. To create a relationship between two tables, keys must be designated and the relationship between those two keys must be defined. To do this, click the key for one table or view and then drag it to the key in the table you’d like to join to. Excel will usually automatically define the type of relationship, e.g., one-to-many, many-to-many, etc.

Notice that there is an EmployeeNo field in the OrderFact table. Connect the Employees table to the OrderFact table by left-clicking on the EMPLOYEE_ID field in the Employees table while still holding down the left mouse button and dragging the cursor over the EmployeeNo field in the OrderFact table. This will connect these two tables as shown in the following image.

Next, let’s get data from an Excel workbook. Now, you can import Excel data directly from within Power Pivot, but you can also link spreadsheets in an open Excel workbook into the data model. To illustrate that, switch to your Excel worksheet and import data using the Data menu option.

On the Data menu, select Get Data → From File → From Excel Workbook and select the Products.xlsx file you previously downloaded. Walk through the wizard to import the data and save the worksheet as Products.

The data will be imported as a data table that is already named. With the table highlighted, navigate to the Power Pivot tab in the Excel toolbar and click Add to Data Model. This imports the data from the table we just created into the data model we have been working on in the previously shown imports.

To create a relationship for this new data, switch back to the Diagram View. Connect the Products table to the OrderFact table using the ProductID column in both tables.

The Refresh button can be used to go out and gather refreshed data from all of the various sources from which you’ve now imported data. It may be helpful to know that we can add calculated columns to our data within our data model as we can with data tables in our worksheets for any columns we might want to transform.