In this post, I am going to show you how simple it is to create a custom calendar using Power Query. If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it.
Different Types of Calendars
There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on p3adaptive.com (25 prior to this one). Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year. There is one problem with this approach however – the date over runs when you are looking at last years’ data in a pivot.
As you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet. It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually). You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.
Building a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below.
First Create a New Blank Power Query Workbook.
The Blank Query option is right at the bottom of the “From Other Sources” menu.
If you haven’t done so already, turn on your Formula bar from the view menu. You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed.
Create a list of dates
The first step is to use the Power Query Language in the formula bar to create a list of dates. Note that the Power Query Language is case sensitive so you must capitalise correctly.
Type in the code =List.Dates
and then press the “Invoke” Button. Note how Power Query gives you some help about the function you just typed in.
Enter a start date, the number (count) of days you want and the granularity of the calendar (step 1 means daily), and press OK. For now we will just select 100 days.
But we don’t actually want 100 days, we want a “dynamic end date”. We need to change this new line of code to create the dynamic end date.
Simply replace the 100 in the formula with this Power Query code substitute
Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2015,1,1))
You just need to make sure the date used above matches the first date in your list. As I am sure you can work out, this code uses a function Duration.Days to count how many days there are between today’s date and the start date you entered. There are other ways to do this, including this one described by Dominik, any approach is fine.
If you want your calendar to have a 1 day lag (ie when you refresh it today, you want all dates up until yesterday’s date, then simply subtract 1 from the formula above like this. Note the minus 1 is outside of the brackets.
Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) – #date(2015,1,1)) -1
Convert the list to a table
Simply click the button on the screen. Just accept the default options on the screen prompt.
Now add an Index column
There is a button for this too. The Power Query team has thought of everything!
The Index column starts at 0 by default (see 1 below). I like my index to start at 1, but it is easy to change. You can either click on the cog icon next to the Added Index step (see 2) or you can simply change it in the formula bar (see 3).
Interested in Learning How to Do this Kind of Thing?
Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.
CLICK HERE to claim your spot.
Add Calendar Metadata
Now it’s time to add your calendar metadata, like days, months, quarters etc. I first renamed my Column1 to “Date” and then inserted a new column
The steps are simple to add a Year column
- Give it a name
- Select your Date column
- Insert it into the editor window
- wrap the column name with the function =Date.Year( )
Add all the other columns
You can add as many columns as you need in your table. I used the above approach to add the following columns
| Column | Power Query Formula to use |
| Month Number | =Date.Month( ) |
| Day | =Date.Day( ) |
| Day Name | =Date.ToText([Date],”ddd”) |
| Month Name | =Date.ToText([Date],”MMM”) |
| Quarter Number | =Date.QuarterOfYear( ) |
So the Calendar Table is now starting to look pretty good.
Create a short year column
To do this, I just duplicated the Year column, converted it to text and then split the column so that I only ended up with the last 2 characters.
I then created a nice little Quarter/Year column using the “Add Custom Column Button” again and some more Power Query Language. =Number.ToText([Short Year]) & “Q”& Number.ToText([Quarter Number],”00”)
I’m just essentially concatenating the Short Year, the Quarter Number with some formatting text added in for good measure.
A bit of tidy up, move some columns and I ended up with this. A great Calendar that automatically updates to today’s date every time you refresh your workbook.
The Code
If you don’t want to go through all these steps yourself, you can just copy and paste my code from here.
let
Source = List.Dates,
#"Invoked FunctionSource" = Source(#date(2015, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow()) - #date(2015,1,1)), #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
#"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}})
in
#"Changed Type2"
All you need to do is copy it from above, then go to Power Query, click on View/Advanced Editor and paste the text into the window
Where to get more information?
I recommend M is for Data Monkey by Ken Puls and Miguel Escobar. I have written a review about this book here. There are also some great reference materials on the Internet for Power Query. I keep a list of the good ones I find on my links page.
My next project is to work through the challenges of a 4/4/5 calendar table. I would love to hear from anyone that has already attempted this using Power Query.
Edit March 2015: Here is a link to the next post covering the 445 calendar.
Matt Allington is a is a professional Power Pivot consultant and trainer based in Sydney Australia.
