Category: Data Model

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Extend the Data Model using calculated columns

Ashwini Tripathi

Recall from my previous post Add a relationship using Diagram View in Power Pivot

Which I have left with below note. I will be continuing from where I left my previous post.

It’s nice when the data in your Data Model has all the fields necessary to create relationships, and mash up data to visualize in Power View or PivotTables. But tables aren’t always so cooperative, so in today’s post will describe how to create a new column, using DAX that can be used to create a relationship between tables.

Open the Olympics Excel sheet which we used in our previous post.

ExcelData-7
Delete the column EditionID from sheet Medals & Hosts Table/Sheet.
ExcelData-8

After deleting the Column EditionID from Table/Sheet Select Update All in Ribbon from PowerPivot Tab. This will update the column in Data Model.

Since we are going to learn creating relationship on calculated fields.

In Hosts, we can create a unique calculated column by combining the Edition field (the year of the Olympics event) and the Season field (Summer or Winter). In the Medals table there is also an Edition field and a Season field, so if we create a calculated column in each of those tables that combines the Edition and Season fields, we can establish a relationship between Hosts and Medals. The following screen shows the Hosts table, with its Edition and Season fields selected
ExcelData-9

Select the Hosts table in Power Pivot. Adjacent to the existing columns is an empty column titled Add Column. Power Pivot provides that column as a placeholder. There are many ways to add a new column to a table in Power Pivot, one of which is to simply select the empty column that has the title Add Column.

In the formula bar, type the following DAX formula. The CONCATENATE function combines two or more fields into one. As you type, AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available. Use tab to select AutoComplete suggestions. You can also just click the column while typing your formula, and Power Pivot inserts the column name into your formula.

=CONCATENATE([Edition],[Season])

When you finish building the formula, press Enter to accept it.

Values are populated for all the rows in the calculated column. If you scroll down through the table, you see that each row is unique – so we’ve successfully created a field that uniquely identifies each row in the Hosts table. Such fields are called a primary key.

Let’s rename the calculated column to EditionID. You can rename any column by double-clicking it, or by right-clicking the column and choosing Rename Column. When completed, the Hosts table in Power Pivot looks like the following screen.
ExcelData-10

Next let’s create a calculated column in Medals that matches the format of the EditionID column we created in Hosts, so we can create a relationship between them.

When you created a new column, Power Pivot added another placeholder column called Add Column. Next we want to create the EditionID calculated column, so select Add Column. In the formula bar, type the following DAX formula and press Enter.

=CONCATENATE([Year],[Season])

Rename the column by double-clicking CalculatedColumn1 and typing EditionID.
ExcelData-11

Next let’s use the calculated columns we created to establish a relationship between Hosts and Medals.

In the Power Pivot window, select Home > View > Diagram View from the ribbon.

Drag the EditionID column in Medals to the EditionID column in Hosts. Power Pivot creates a relationship between the tables based on the EditionID column, and draws a line between the two columns, indicating the relationship.
ExcelData-12

Now you can see the relationship between Host & Medal table is established on calculated field EditionID in both the tables.

Stay tuned for more details on this topic. I will come up with next step in this series in my upcoming post.

Till then keep learning & practicing.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Add a relationship using Diagram View in Power Pivot

Ashwini Tripathi

Open the Workbook which we prepared in the last exercise in our previous post.

Add Excel Sheet/Table to the PowerPivot Data Model

  • In the Power Pivot window, in the View section, click Diagram View.
  • Use the slide bar to resize the diagram so that you can see all objects in the diagram. Rearrange the tables by dragging their title bar, so they’re visible and positioned next to one another. Notice that all tables are unrelated to each other, we will create relationship among these tables.

After adding relationship the Diagram will Look as below.

ExcelData-6

  • Let start creating the relationship.
  • Sports->SportID click and drag to Disciplines->SportID and release. You will see the relation is built between these two tables on SportID.

Similarly follow to create other relations too as shown in below image.
ExcelData-5

You notice that both the Medals table and the Events table have a field called DisciplineEvent. Upon further inspection, you determine that the DisciplineEvent field in the Events table consists of unique, non-repeated values.

The DisciplineEvent field represents a unique combination of each Discipline and Event. In the Medals table, however, the DisciplineEvent field repeats many times. That makes sense, because each Discipline+Event combination results in three awarded medals (gold, silver, bronze), which are awarded for each Olympics Edition the Event is held. So the relationship between those tables is one (one unique Discipline+Event entry in the Disciplines table) to many (multiple entries for each Discipline+Event value).

  • Create a relationship between the Medals table and the Events table. While in Diagram View, drag the DisciplineEvent field from the Events table to the DisciplineEvent field in Medals. A line appears between them, indicating a relationship has been established.
  • Click the line that connects Events and Medals. The highlighted fields define the relationship, as shown in the above screen.
  • To connect Hosts to the Data Model, we need a field with values that uniquely identify each row in the Hosts table. Then we can search our Data Model to see if that same data exists in another table. Looking in Diagram View doesn’t allow us to do this. With Hosts selected, switch back to Data View.
  • After examining the columns, we realize that Hosts doesn’t have a column of unique values. (Assume EditionID Field is not available in the Host & Medal Table, in my case I have already added this Field in my workbook I shared). In such case we’ll have to create it using a calculated column, and Data Analysis Expressions (DAX).

It’s nice when the data in your Data Model has all the fields necessary to create relationships, and mash up data to visualize in Power View or PivotTables. But tables aren’t always so cooperative, so the next post will describe how to create a new column, using DAX that can be used to create a relationship between tables.

We will see how to work with calculated fields in our next upcoming post.

Data Model, Development Tips, Excel, How To, PowerPivot, Report

Add Excel Sheet/Table to the PowerPivot Data Model

Ashwini Tripathi

Download the Excel file from the link provided in previous post.

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

Open the Workbook:

ExcelData-3

Select sheet one by one and Add to Data model of PowerPivot.

ExcelData-4

Make sure all Table/Sheet is added to Data Model of PowerPivot.

I have create a sample Excel File for you which you can down load from the Shared Path using link: http://1drv.ms/1GpZFd4

This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.

You can find the link on the Menu of my blogsite too to access the shared files.

I will be using this file for couple of walkthrough in my upcoming posts.

Data Model, Development Tips, Excel, PowerPivot

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

Ashwini Tripathi

You can copy & Paste data from an Excel workbook, or import data from a table on a web page, or any other source from which we can copy and paste into Excel. In the following steps, you add the data from a table.

  • Insert a new Excel worksheet, and give it the name desired any meaningful name that describes the data.
  • Select and copy the data, including the table headers.
  • In Excel, place your cursor in cell A1 of the above created worksheet and paste the data.
  • Format the data as a table. You press Ctrl + T to format the data as a table, or from HOME > Format as Table. Since the data has headers, select My table has headers in the Create Table window that appears.

ExcelData-1

  • Name the table. In TABLE TOOLS > DESIGN > Properties locate the Table Name field, and type desired name.

ExcelData-2

  • Format your data using Excel std. formatting tool.
  • Save the workbook.This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.
  • You can find the link on the Menu of my blogsite too to access the shared files.
  • I have create a sample Excel File for you which you can down load from the Shared Path using link: http://1drv.ms/1GpZFd4

I will be using this file for couple of walkthrough in my upcoming posts.