Guest post by Dominik Petri, who I am stoked to say is translating the book into German!  Today he shares a cool “hybrid” technique that blends two of my favorites things – Data Market and Power Query!

Take it away, Dominik…

Selecting a DateStream calendar from Power Query Navigator taskpane


As you might already know from Rob’s “The Ultimate Date Table” post, you need a separate calendar table to unleash the power of Power Pivot’s time intelligence functions. You want one for free? Fully customizable? Running up to a variable end date? Updated automatically every time you open your workbook? The time has come…

Get a great calendar table – for free

Boyan Penev has put together various calendar tables that you can download from Azure DataMarket directly into Power Pivot – for free. Again, see Rob’s postfor details. So far, so good. But it is a pain to filter the data range you want to import. But there is Power Query!

Filter and shape the calendar with Power Query

Instead of importing the calendar into the Power Pivot window, I use Power Query (the tool formerly known as Data Explorer). Power Query is part of Microsoft’s Power BI family. You can download the free add-in from here.

By using Power Query, you can easily filter the date range. Here is how:

Connect to DateStream with Power Query

In Excel, switch to the POWER QUERY tab and click From Other SourcesFrom Windows Azure Marketplace. In the Navigator pane on the right side of your screen, click DateStream. If you don’t see DateStream, you are not subscribed to DateStream in Windows Azure Marketplace. Check Rob’s initial blog post for how to subscribe.

The choice is yours: Select from eleven different calendars

Currently, there are 11 tables to choose from. Simply hover the mouse over a calendar an you’ll see a preview:

Selecting a DateStream calendar from Power Query Navigator taskpane

Filter Down to Relevant Dates

I go for the BasicCalendarUS. A double click on the calendar name opens the Query Editor. The calendar starts on January 1, 1900! Way too much data! The good news: You can easily narrow the date range by clicking the down arrow in the DateKey column and select Date/Time Filters > Between

Using the Date Time Filter in the Power Query Editor

Now I enter the start and end date in the Filter Rows dialog box:

Select the start and end date for the date filter in the Power Query Editor

After a few seconds, the preview is updated. If you don’t want to import every column, just right click on the column’s header and choose Remove from the context menu.

Import the calendar directly into Power Pivot

Since I want the calendar in Power Pivot and not on a worksheet, I check Load to Data Model in the Query Settings task pane and uncheck Load to worksheet. The query’s name will become the table’s name in the Power Pivot window. Feel free to change the name…

Settings in Power Query Task Pane

I am done (at least for now) and click Apply & Close on the Home tab:

Click Apply and Close in the Power Query Home Tab

Back in the Power Pivot window I see the new table called BasicCalendarUS with 5,479 days/rows.

Making the End Date dynamic

So far, I have hard wired the end date into the query. Not my style. Yours? I’d rather have the calendar end today. Or at the end of the current week. Or current month. So let’s make it dynamic!

Power Query comes with its own language called “M”. I can already see the eyes of the James Bond fans lighten up! Let’s get back to the query: Return to Excel and double click the query in the Workbook Queries task pane:

Workbook Queries Task Pane

Like in Excel, there is a formula bar above the grid. The formula contains two #datetime functions that represent the dates you entered in the Filter Row dialog box:

DateTime function in the Power Query Formula Bar

Since I want the calendar to end today, I need to replace the second #datetime function. In Excel I would use TODAY(). The corresponding function in the Power Query formula language is called DateTime.LocalNow(). Be careful: The function names are case-sensitive!

Replace the DateTime function with DateTime.LocalNow

Let’s say the data I want to analyze is lagging by 2 days. Therefore, I want the calendar to end two days before today. In Excel, I would use TODAY()-2. In “M” I need to subtract a duration value:

Use the Duration function to add or subtract days from DateTime.LocalNow

More variations for a dynamic End Date

It is also possible to have the calendar run up to the current week’s end. Here’s how:

Changing the operator in the Formula Bar

Notice how I changed the operator from “is equal or smaller than” to “is smaller than”? I don’t know why, but Date.EndOfWeek(DateTime.Local.Now()) returns the first day of next week. It should return the last day of the current week with a timestamp of 23:59:59.9999 I guess it gets rounded up. I “cure” this by using the “is smaller than” operator Smiley

If you prefer the calendar to end at the end of the month or at the end of the year, use Date.EndOfMonth or Date.EndOfYear instead.

The icing on the cake: Automatically updated calendar

With a dynamic end date comes the need to update the calendar. Very convenient if this is done automatically every time I open the workbook. Back in the Excel window I open the Connection Properties dialog (DATA – Connections – Connections) and check Refresh data when opening the file.

Have the query updated automatically every time the workbook is opened