Please note javascript is required for full website functionality.


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.

I call my new query ‘Calendar’.

I will use the M function List.Dates(). If I just input the function for the step, Power Query will show the parameters required.

I start by using the dates for this year, incrementing by one (1).

When I choose to invoke, my list of dates is created:

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)) 

When I enter this, my ‘Calendar’ query will change from a function to a list.

I delete the ‘Invoked Function’ query (which I just broke by changing ‘Calendar’). Then, I transform my list to a table:

I accept the default options.

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.

When I enter this, I get the date I have specified.

I rename this step ‘StartDate’. I can now reference this step in my Source step.

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

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.

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.

I have too much information again, so I need to use Duration.Days() to get the section I want.

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.

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!