Adding columns to your data model
Before we start any activity, let us switch to the Design tab in the Power Pivot window. Here, we have some icons that can help us add new columns to our existing data. We can add a new column by selecting the Add icon under the Design menu, which will highlight the last column in our data, or by selecting this column directly.
Figure 4. 9 – Adding a new column in Power Pivot
Let’s try an example of adding a new column to the customer data. We want to calculate the age for each customer as a new column.
To do this, you can go to the Customer tab:
1. Select the last column, which will have Add Column as the header.
2. Type the name of the new column into the formula bar.
3. After selecting the column in the formula bar, begin the formula with the name of the column followed by a colon (:) and an equals sign (=).
Figure 4. 10 – Creating a formula for your calculated column
Here is an example using Age of Customer:= .
The DAX function to calculate age is DATEDIFF. This is similar to what we have in native Excel, but with an extra F.
Complete the following formula and press Enter:
Age of Customer:=DATEDIFF([Birthdate],TODAY(),YEAR)
Birthdate in the formula is an existing column in the Customer Data table. When selecting an existing column in a DAX formula, you can start with the square bracket. This pulls a list of all the columns in the current dataset. You can select or press Tab to complete the selection process. TODAY is another DAX function that gives you the current date. YEAR is one of the many options in DATEDIFF if you want to return the age of the customer in YEARS. Other types of internal values for this parameter are DAY, WEEK, MONTH, QUARTER, and so on.
You have now created a calculated column in your data model. Ideally, calculated columns should be created in Power Query. This optimizes the performance of your datasets, especially when you have a large volume of data.
Your new calculated column should look like that in the following screenshot:
Figure 4.11 – Creating a calculated column in Power Pivot
