Chapter 24 Dynamic Calendar Tables

When working with Power Pivot in Excel 2010 and 2013, you need to provide your own calendar table for your models. This is really easy in organizations with a cooperative IT department that publishes a calendar for users.

If you don’t live in an organization like that, however, things are more difficult. You can reach out to web sources, but those don’t work when you’re tinkering with your model while sitting in an airplane or on the way out to visit your operations in that mine in Africa. You can also build your calendar in an Excel table, but this leads to challenges when the data keeps expanding, as the date table doesn’t automatically expand for you.

For those users, Power Query has come to the rescue. As you’ll see, it is super easy to set up a fully dynamic calendar in Excel using Power Query. And building on the techniques from Chapter 23, you can drive the whole thing based on the value in a few Excel cells.

Creating a Dynamic Calendar Table

Creating a full dynamic calendar for your solution requires a total of four steps:

1. Add a parameter table to hold the calendar boundaries.

2. Implement the fnGetParameter function to retrieve the boundaries.

3. Build the basic dynamic calendar framework.

4. Add the required calendar columns.

Building on the methods explored in Chapter 23, in this chapter you’ll see that this process is actually quite quick to set up.

Step 1: Adding a Parameter Table

For this example, you’ll start from a completely blank workbook, although you could easily retrofit this into an existing model as well. If you happen to already have a Parameter table in your solution, you can simply add new fields to it. If not, you’ll have to build the table from scratch.

Remember the key pieces here as you build your table:

• The table must be called Parameters
• There must be a Parameter column and a Value column

You should build the table to hold the start and end dates:

• Start Date 1/1/2014
• End Date =EOMONTH(TODAY(),0)

When the setup is complete, the table will look as shown below:

Figure 308 The Parameters table.

The EOMONTH() function has the following syntax:

=EOMONTH(start_date,months)

If you use 0 for the months parameter, the function will return the last day of the given month. If you provide -1, it will be the end of the previous month, and if you use a positive value of 3, the function will give you the end of the month 3 months after the provided date. This function is very useful when driving your calendar table.

Step 2: Implementing the fnGetParameter Function

Once the table is built, you can create the fnGetParameter function:

• Open the Ch24 Examples\fnGetParameter.txt file
• Copy the entire contents of the text file
• Create a new query → From Other Sources → Blank Query
• Go to Home → Advanced Editor
• Highlight all the code in the window → Ctrl+V → Done
• Rename the query fnGetParameter

The fnGetParameter function is now installed as well, ready to be used:

Figure 309 The fnGetParameter function is ready to go.

Step 3: Building the Basic Calendar Framework

With the groundwork quickly laid, you can now get to the real task at hand: building the calendar. Rather than exit the Power Query editor, you can create a new query right inside it:

• Go to Home → New Source → Other Sources → Blank Query
• Rename the query Calendar

You now have a new query with nothing in it at all:

Figure 310 Starting the calendar from a clean slate.

An easier way to start building the calendar, believe it or not, is to start from a simple list. Click inside the formula bar and enter the following formula:

={1..10}

This creates a list from one to ten:

Figure 311 You now have a simple list.

Next, convert this list into a table and see what happens if you change the resulting column into dates:

• Go to List Tools → Transform → To Table
• Leave the default options and click OK
• Right-click Column1 → Change Type → Date
• Right-click Column 1 → Rename → Date

Although it’s not exactly the date range you’re after, you do actually get the beginning of a calendar table:

Figure 312 It’s a calendar table but a little out of date!

Even though it doesn’t necessarily look like it, you’re in really good shape here. Remember that parameter table you set up? What if you inserted a couple of steps at the beginning of this query to extract the start and end dates and tried to feed those into the list instead of using 1 to 10? Try it:

• Go to Home → Advanced Editor
• Immediately after the let line, add the following two lines of M code:

  startdate = fnGetParameter("Start Date"),

  enddate = fnGetParameter("End Date"),

• Click Done

At this point, it’s a good idea to just check both steps and make sure they are actually returning the date that you’re expecting them to return.

Figure 313 startdate is returning a date as expected.

With the variables now holding the start and end dates, you can sub them into the Source line, replacing the 1 and 10 you put in as placeholders earlier:

• Select the Source step
• Edit the formula in the formula bar to read as shown below:

  = {startdate..enddate}

• Press Enter

Unfortunately, things go sideways:

Figure 314 What in the world does this mean?

This error message isn’t extremely clear. Better wording would indicate that Power Query can’t use the .. operator unless it has numbers on either side. You provided dates, not numbers. And while you know that dates are just numbers anyway, Power Query isn’t quite as forgiving.

This is not a show-stopper by any means. It just means that you need to convert these dates into their date serial numbers. Once they are values, Power Query will be able to work with them. Here’s what you do:

• Go to Home → Advanced Editor
• Replace the startdate and enddate lines with the following:

  startdate = Number.From(fnGetParameter("Start Date")),

  enddate = Number.From(fnGetParameter("End Date")),

• Click Done
• Select the startdate step

startdate is now converted to the date’s serial number, 41640. If you step to the end of the query, you can see that you now have a working calendar that runs from January 1, 2014, through the ending date provided by your Excel table:

Figure 315 The Calendar table is ready for use.

Step 4: Adding Required Calendar Columns

With the Calendar table set up and covering your date range, it’s now time to add the required columns to it. This is actually pretty easy:

• Select the Date column → Add Column → Date → Year → Year
• Select the Date column → Add Column → Date → Month → Month
• Select the Date column → Add Column → Date → Day → Day

As you can see, the Calendar table is really starting to take shape:

Figure 316 Building out the required columns in the Calendar table.

Power Query has a variety of formats that are very easy to add to the table. However, there are also some formats that don’t exist.

Power Query Date Functions

Many of Power Query’s date functions are different from Excel’s—some subtly and others not so subtly. Therefore, the following pages give you the means to easily work out in Power Query what you already know how to do in Excel.

Excel Date Functions

Excel has a variety of date functions that can be of great benefit when building the Parameters table to drive your calendar. Each of them is explicitly for use in Excel (not Power Query) and can be used to drive either start or end dates to give yourself a range that is scoped nicely to your needs.

To Return	Formula
The current date	TODAY()
End of the current month	EOMONTH(TODAY(),0)
End of last month	EOMONTH(TODAY(),-1)
End of next month	EOMONTH(TODAY(),1)
End of the current year	EOMONTH(TODAY(),12-MONTH(TODAY()))
End of year for date in B25	EOMONTH(B25,12-MONTH(B25))

Date Function Equivalents: Extracting Date Parts

Like Excel, Power Query also has functions specifically targeted at returning parts of dates or offsetting dates. Some key conversions you will want to know are listed here:

Excel Formula	Power Query Version
DAY([Date])	Date.Day([Date])
MONTH([Date])	Date.Month([Date])
YEAR([Date])	Date.Year([Date])
WEEKNUM([Date])	Date.WeekOfYear([Date])
WEEKDAY([Date])	Date.DayOfWeek([Date])
EOMONTH([Date],0)	Date.EndOfMonth([Date])
EOMONTH([Date],-1)+1	Date.StartOfMonth([Date])

Date Function Equivalents: Adding Dates

You’re likely to be frustrated if you try to take a valid date or time and just add a value to it in order to increment it. Even though you know that all dates are based on a date serial number, Power Query cannot implicitly convert the data type like Excel can. For this reason, there are a collection of Date.Addx functions to use for this purpose:

Adding x	Excel Formula	Power Query Version
Days	[Date]+x	Date.AddDays([Date],x)
Months	EDATE([Date],x)	Date.AddMonths([Date],x)
Years	EDATE([Date],12*x)	Date.AddYears([Date],x)
Weeks	[Date]+7*x	Date.AddWeeks([Date],x)])

Date Function Equivalents: Returning Text Dates

In order to convert and return dates in a textual format, you would use the TEXT() function in Excel. In Power Query, the equivalent functions are provided by Date.ToText(), but they have an additional wrinkle: Not only is the function case sensitive, but so are the parameters.

To Return	Excel Formula	Power Query Version
Sun	Text([Date],"ddd")	Date.ToText([Date],"ddd")
Sunday	Text([Date],"dddd")	Date.ToText([Date],"dddd")
Aug	Text([Date],"mmm")	Date.ToText([Date],"MMM")
August	Text([Date],"mmmm")	Date.ToText([Date],"MMMM")
Aug 9, 2015	Text([Date],"mmm d, yyyy")	Date.ToText([Date],"MMM d, yyyy")
Aug 09, 2015	Text([Date],"mmm dd, yyyy")	Date.ToText([Date],"MMM dd, yyyy")

Dealing with Date Conversion Errors

Many of the functions listed above require a date or datetime data type as an input and will return an error if an alternate data type is provided. To guard against this situation, you can wrap the [Date] column input with the Date.From function as shown here:

=Date.AddDays(Date.From([DateColumn]),1)