Power Query: Update Your Calendar
21 October 2020
Welcome to our Power Query blog. This week, I look at calendar creation.
It’s been a while since I have looked at how to create a calendar, and Power Query has moved on since then. It’s time to bring my calendar creation approach up to date.
I will start by creating a list of dates, since that is what a calendar essentially is. I create a Blank Query from the ‘Other Sources’ option on the ‘New Query’ section of the ‘Get & Transform’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I call my new query ‘Calendar’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I will use the M function List.Dates(). If I just input the function for the step, Power Query will show the parameters required.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I start by using the dates for this year, incrementing by one (1).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
When I choose to invoke, my list of dates is created:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
The M code used is:
= Calendar(#date(2020, 1, 1), 366, #duration(1, 0, 0, 0))
Because I invoked a function, I now have two queries, ‘Calendar’ and ‘Invoked Function’. I only want to keep the ‘Calendar’ query, so I copy the M code generated in the source step of ‘Invoked Function’ into my ‘Calendar’ query. I don’t enter Calendar(#date… since that would create circular logic; I need to amend the M code to:
= List.Dates(#date(2020, 1, 1), 366, #duration(1, 0, 0, 0))
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
When I enter this, my ‘Calendar’ query will change from a function to a list.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I delete the ‘Invoked Function’ query (which I just broke by changing ‘Calendar’). Then, I transform my list to a table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I accept the default options.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I have also changed the column name to Date and transformed the data type to Date.
I need to make some changes to my source step so that the calendar is more flexible. My source step is currently:
= List.Dates(#date(2020, 1, 1), 366, #duration(1, 0, 0, 0))
The start and end dates are fixed; I want these to be easily changed in my query instead. I can add a step to my code which will contain the start date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
When I enter this, I get the date I have specified.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I rename this step ‘StartDate’. I can now reference this step in my Source step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image12.png/a1537847463e660a31158c8032525438.jpg)
The M code I am now using for my Source is:
= List.Dates(StartDate, 366, #duration(1, 0, 0, 0)
There is no end date parameter as such, just the number of dates generated. I can choose to always create the calendar ending today, in which case I need to find out now many days until today. I start by finding out today’s date in M.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image13.png/917da985be13220165c8d2823e95344f.jpg)
The M code I have used is,
= DateTime.LocalNow()
which gives me the exact moment. I only need the date, so I can use DateTime.Date() to extract the data I want.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
The M code I have used is:
= DateTime.Date(DateTime.LocalNow())
I rename this step ‘Today’ and add a step to calculate the interval between today and my start date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I have too much information again, so I need to use Duration.Days() to get the section I want.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image16.png/d082e3477129350b8a2a589156028e63.jpg)
The M code I have used is:
= Duration.Days(Today - StartDate)
I can rename my step Calendar_Length. I need to change the Source step to use Calendar_Length.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/203/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
The M code is now:
= List.Dates(StartDate, Calendar_Length, #duration(1, 0, 0, 0))
I have the basis for my calendar, and I can easily identify and change the start date. Next time, I’ll expand the calendar to make it more useful. I’ll also point out the deliberate mistake in the Calendar_Length.
Come back next time for more ways to use Power Query!