Sign in or start a free trial to avail of this feature.
1. Adding a Date Table
When conducting time based analysis in Power Pivot, you need to use a date table. We will learn what date tables are, why they are needed and how to add them to Power Pivot.
Need for Date Tables
- Often the date field in a dataset does not include every date between the first date and the last date
- Power Pivot can only use dates that are in the data model
- A date table contains every date between the start and end of the dataset
Adding a Date Table
- Adding a date table and creating a relationship with the data model means that all the necessary dates are in the data model
- Using the date field from the date table, time intelligence functions will work correctly
- In Excel 2016, date tables can be added automatically
Alt H, SA – Sort column in ascending order
Alt H, W – Open Diagram View
Alt H, D2 – Open data view
In the previous course, we went over many of the features available in Power Pivot, giving you a good base for how the program works.
In this course, we'll pick up where we left off and focus on how to get more out of our data model and the different options available for presenting information. In the first few lessons, we'll look at some more features of Power Pivot and how to format and organize our data model. We'll then move and see how we can visualize geographical information with 3D Maps.
In the last few lessons, we'll see how we can use Power View to create interactive reports from our data.
Throughout this course, we'll use an expanded version of the same professional services data set that we used previously.
We'll start by looking at date tables.
Date tables add a full range of dates to a dataset and are essential for time intelligence analysis.
Time intelligence incorporates various analysis functions involving dates, and is a common task in Power Pivot. We'll cover time intelligence in more detail in our DAX courses. However it's worth drawing attention to date tables now.
Our goal in this lesson is to add a date table to our data model that covers any date gaps in our dataset.
We'll start by looking at our transaction table.
Let's select the date column and sort it from oldest to newest using the shortcut Alt, H, SA.
This column has dates ranging from August to November 2016.
Looking through the column, we can see that not every date in the time period is present in the data set.
Let's add a new column to the dataset and call it Next Day.
We'll now enter a formula which is simply the Next Day formula applied to the Dates column.
We expect this column to simply return the day after each transaction.
We'll press Enter to create the column.
And change the date format to match the existing column.
We'll also expand the column to see the full date.
As we scroll down, we can see that many rows have blank entries for the Next Day column.
For example, there are several blank entries corresponding to the transaction date of October 27th.
The issue is that no transactions took place on October 28th. And as a result, the date does not exist in the data model.
Power Pivot can only understand and use dates that are already in the model. It is unable to add new dates to the model.
The solution to this problem is to add a date table to the data model.
The date table is a table which contains all the dates within the time range of the model. Let's move over to the date table Excel file. You can find this file in the lesson notes below the video. This sheet contains a complete column of dates void of any gaps.
We also have other date information such as day, week number, and month, that could be useful for our model.
Note that the date tables should start before the first date near model and run after the final date. In this case, the date table runs for the full length of 2016.
Let's go back to Power Pivot and add this date table.
We'll choose to get data from another source via the Home tab with the shortcut Alt, H, O.
We'll select Excel file, browse to the date table, select the option to use the first row as column headers, and click next.
We'll only select the sheet with dates and finish the import.
We'll now link this table to the model by creating a relationship.
We'll navigate to diagram view and expand the table so all fields are showing.
The date table should be related to the date column in the transactions table, so we'll drag the date field from the transactions table to the date field in the date table.
We'll return to data view and change the formula in the Next Day column to use the date field from the dates table.
If we scroll through the table, we can see the column is now fully functional with no more blank rows.
This is because our dates table includes all the necessary dates to make this column work.
In Excel 2016, there's a new feature that allows you to create date tables automatically. To see this feature, we'll navigate to the design tab, select date table, and then new.
This creates a date table similar to the one we've just imported from Excel.
However, this table doesn't contain all the columns from the sheet we imported.
We'll delete this new table from the data model by right-clicking and selecting delete.
As we mentioned previously in this lesson, date tables are required for any effective time intelligence analysis with Power Pivot.
If you've ever had date-related problems in Power Pivot, adding a date table may be the solution.
In the next lesson, we'll look at how we can use hierarchies to order data in our model.