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.
To explore more Kubicle data literacy subjects, please refer to our full library.
Lesson Goal (00:04)
The goal of this lesson is to add a date table to our model that covers any gaps in our data set.
The Need for Date Tables (00:51)
A date table is a table that contains all the relevant dates in your data model, without any missing dates. Using a date table is essential when conducting any sort of time intelligence analysis using Power Pivot.
Date tables are needed when your data set does not include every date between the beginning and end of your data. For example, our data set features a series of transactions over a three month period. However, not every date in the period has a transaction. If we create a formula that returns dates, the formula will only be able to return dates on which a transaction occurred. The formula returns a blank result for any other date. By inserting a date table, we can ensure that there are no missing dates in the model, which will mean our time intelligence formulas will work correctly.
Layout of a Date Table (02:59)
You can create a date table in an Excel spreadsheet or in any other format that can be imported into Power Pivot. The table should contain a column containing dates. The dates should start on or before the first date in your data set, and end on or after the last date in your dataset, with no gaps in between. Optionally, you can also include other columns giving more information about each date, such as the day of the week or the week number. These columns can be helpful in conducting time intelligence analysis.
Adding the Date Table (03:36)
We can import the date table into Power Pivot by following the normal data import process. In our case, we export the Excel sheet using the Table Import Wizard. After importing the table, we need to ensure we create the appropriate relationships with the existing tables in the model.
After we add a date table, we should ensure that any formulas we write which return a date use the date column from the date table. This will ensure that the formulas return a date value instead of a blank value.
Creating a Date Table Automatically (05:04)
In Excel 2016, we can create date tables automatically through Power Pivot. To do this, we select Date Table, then New, from the Design tab. This creates a date table containing all the dates from the start to the end of your model.
Creating a date table automatically is quick and easy, but it has some limitations. The table won’t include dates before the start of your dataset or after the end, and it may not include all the additional columns that you might include in your own date tables. However, it’s a good option to use if these limitations are not a problem for you.
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.