Create Date Table or Calendar in Power Query M | including Script

This post describes how to create a dynamic date table using Power Query. Virtually every Power BI solution benefits from a date table. The date dimension provides options to view your data from different perspectives. You can show your measures by day name, week, month name, quarter etc. It also allows for more complex calculations. You could use it for calculating the amount of working days between order date and shipping data, but you can also use it to calculate average sales made on national holidays.

Regardless the use case, you will find yourself coming back to creating a date table again and again. To give you some perspectives on it, this post delves into building a dynamic calendar table. The article focuses on generating a date series, creating columns with the user interface and then focuses creating common custom columns.

1. Add Parameters

You will start by creating a parameter for both start and end date. To add these, open Power Query and go to Manage Parameters. Then click on New and add the following parameters:

  1. Name: StartDate. Type: Date, Current Value: 01-01-2021.
  2. Name: EndDate, Type: Date, Current Value: 31-12-2022.

You end up with the below two parameters which you can can reference in the following chapters.

2. Create a List of Dates

The foundation of a calendar table is a list of dates. Since many time intelligence functions in DAX require you to have a consecutive list of dates it is important not to have any gaps between the dates in your date dimension.

You can start by creating a blank query. Right click in the queries pane, select New Query -> Blank Query. You end up with an empty query to start off your calendar.

The next step is to create a list of consecutive dates. If lists are new to you make sure to check out the complete guide to lists in Power Query.

You can create a list of dates in four different ways. Which of the methods you choose is unimportant. I show you these different ways hoping you may learn something along the way.

2.1. List.Dates

The first method makes use of the List.Dates function in Power Query. It’s a function that takes three arguments as input. A start date (as date), the number of intervals to add (as number) and the size of each interval (as duration).

An example is the following expression that creates a list with 5 consecutive dates. To see this in action, add the below code in the formula bar of your blank query:

= List.Dates(                      // Creates a list of dates
     #date(2021, 10, 15),          // Starting from Oct 15th 2021
     5,                            // Creating 5 steps
     #duration(1, 0, 0, 0)         // Of 1 Day
 )

If the #duration() syntax is new to you it is good to know it takes 4 arguments as input: days, hours, minutes, seconds. The above formula therefore increments with 1 day intervals. Changing the duration from 1 day to 2 days, would create a list that increments with 2 days per step.

Next you can try to make this formula more dynamic. Instead of hardcoding a start date, adjust the original formula and include the parameter called StartDate:

= List.Dates(
     StartDate,                      // Uses start date parameter
     5, 
     #duration(1, 0, 0, 0)
  )

To also make the total amount of intervals dynamic, you can retrieve the amount of days between the end date and start date. Function argument 2 requires a number as input and one way to the difference in days is by using the Duration.Days function. Just make sure to add +1 at the end, or your date table will miss a day.

= List.Dates(
     StartDate, 
     Duration.Days( EndDate - StartDate ) + 1, // Number of steps
     #duration(1, 0, 0, 0)
  )

The generated date series is now formatted as a list. The last step is to convert this list to a table. You can do that by selecting To Table in the Transform tab. This generates the code:

= Table.FromList(
     Source, 
     Splitter.SplitByNothing(), 
     null, 
     null, 
     ExtraValues.Error
  )

To save yourself the step of renaming the new column (Column1) later you can make an adjustment to the previous formula. You can do this by writing the desired column name within curly brackets in argument 3.

= Table.FromList(
     Source, 
     Splitter.SplitByNothing(), 
     { "Date" },                       // Adds Column Name
     null, 
     ExtraValues.Error
  )

2.2. Autofill Dates in List

Another way to create a consecutive series of dates is to use the auto-fill functionality for lists in Power Query. To see how this works, inspect the following examples that generate a list:

= {1..10}          // Creates list: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
= {"a".."k"}      // Create list: a, b, c, d, e, f, g, h, i, j, k

You can use this functionality to create a list that starts at the StartDate and reaches up to the EndDate. Yet because this functionality does not work with dates, you first need to transform the dates to their corresponding numbers. The following is a completely valid syntax:

= { Number.From( StartDate ) .. Number.From( EndDate ) }

The number 44197 here relates to the date January 1st 2021. Once you have this list, you can transform it to a table as shown in method 1. You are now left with a column with numbers. The last thing to do is to change the column Type to Date. And with that, you arrive at the list with consecutive dates.

2.3. List.Numbers

The third method is similar to Method 2. Yet instead of using the autofill functionality, you use the List.Numbers function.

= List.Numbers( 
     Number.From( StartDate ), 
     Duration.Days( EndDate - StartDate   ) + 1 
  )

The first argument shows the starting number corresponding to the StartDate. And the second argument provides the amount of increments to add to the list in steps of 1.

2.4. List.Generate

A more advanced way to generate a series of dates is by using List.Generate.

= List.Generate(  
     () => Number.From( StartDate ),  // initial value
     each _ <= Number.From( EndDate), // run until EndDate
     each _ + 1,                      // increase 1 for each step, 
     each Date.From( _ )              // return date from values
  )

What the function in above example does is:

  • argument 1: shows the initial value, which is the number corresponding to the StartDate
  • argument 2: instructs List.Generate to perform the function of argument 3 for as long as the number is smaller or equal to the number corresponding to the EndDate.
  • argument 3: provides the function to perform. It instructs to increment each step generated by 1, which corresponds to 1 day.

The List.Generate function is complex and powerful function deservering its own article. For a better understanding make sure to check out my explanation on List.Generate in Power Query with Easy Examples.

3. Adding Columns to Date Table

For as all of the 4 methods work to create your calendar, feel free to pick your favorite. They help in building the foundation of your calendar table, the consecutive list of dates. The next step is to add relevant columns to your calendar. You can easily add most columns through the user interface, which is what I try to teach people as a first step.

3.1. Using the User-Interface

Navigate to the Add Column tab and make sure the Dates column is selected. Then click on Date icon. The button will only be available when working with a DateTime or a Date column. After clicking, the below dropdown provides you with several options.

And this is where Power Query makes it really easy for you to add new columns. For example, clicking Month and selecting Name of Month, creates a column with the Month Name. To then add another column, repeat the process by again selecting the date column and then choosing another column for your date dimension.

You can have a look at the below table to inspect the available options. To make things easy for you I have added sample results in the comments behind each function.

                             // Adds Aging Duration (287.00:00:00)
Date.From( DateTime.LocalNow()) - [Dates] 

Date.Year([Dates])           // Adds Year Number (2021, 2022)
Date.StartOfYear([Dates])    // Adds Start of Year Date (01-01-2021)
Date.EndOfYear([Dates])      // Adds Start of Year Date (31-12-2021)

Date.Month([Dates])          // Adds Month Number (1-12)
Date.MonthName([Dates])      // Adds Month Name (January - December)
Date.StartOfMonth([Dates])   // Adds Month Start Date (01-02-2021)
Date.EndOfMonth([Dates])     // Adds Month End Date (28-02-2021)
Date.DaysInMonth([Dates])    // Adds Days in Month (31, 28, 30 )

Date.QuarterOfYear([Dates])  // Adds Quarter Number (1-4)
Date.StartOfQuarter([Dates]) // Adds Quarter Start Date (01-04-2021)
Date.EndOfQuarter([Dates])   // Adds Quarter End Date (31-03-2021)

Date.WeekOfYear([Dates])     // Adds Week Number of Year (1-53)
Date.WeekOfMonth([Dates])    // Adds Week Number of Month (1-5 )
Date.StartOfWeek([Dates])    // Adds Week Start Date (18-01-2021)
Date.EndOfWeek([Dates])      // Adds Week End Date (24-01-2021)
Date.Day([Dates])            // Adds Day of Month (1-31 )
Date.DayOfWeek([Dates])      // Adds Day of Week (1-7)
Date.DayOfYear([Dates])      // Adds Day of Year (1-365)
Date.DayOfWeekName([Dates])  // Adds Day Name (Monday - Sunday)

// Adds Start of Day (#datetime(2021, 10, 10, 0, 0, 0)
Date.StartOfDay([Dates])     
// Adds End of Day (#datetimezone(2021, 5, 17, 23 ,59 ,59.999 , -7, 0)
Date.EndOfDay([Dates])  


3.2. Commonly used Custom Columns

The columns you have seen so far probably meet 90% of your needs.The other missing 10% requires more customization from your side. This chapter deals with the most important custom date columns. The code in below examples you can add by going to Add Column -> Custom Column. You can then paste the code in the Custom Column Formula box.

The first column I recommend adding is the Month Name Short column. This is an abbreviated version of the month name. So January, February, March turn into Jan, Feb, Mar. It may seem as if this is always the first three letters of the Month Name. Yet depending on the language you use, this is not always true. In Dutch for example we have Januari, Februari, Maart which we turn into Jan, Feb, Mrt.

As you can see March does not transform nicely with the extraction of the first three letters. There’s a hack for this that works in all languages. You can use the Date.ToText function. The second argument of this function allows you to provide all kinds of formatting options that you may know of other applications like Excel.

Depending on the settings of your Power BI file, the result may be in your desired formatting. But what happens when you open your file on a different machine that perhaps has a different default language? It may actually change the output of your queries. To build a more robust solution, you can force a culture in the third optional argument of the Date.ToText function. For a list of available culture codes you can go here. For Dutch this is “nl-NL” and the general English one is “en-EN”. Once you find your desired culture you can add it like:

     // No culture, returns Month Name Short (Jan, Feb, Mar, Apr) 
= Date.ToText( [Dates] , "MMM" )  

     // Dutch culture, returns (jan, feb, mrt)
= Date.ToText( [Dates], "MMM", "nl-NL") 

     // English culture, returns (Jan, Feb, Mar)
= Date.ToText( [Dates], "MMM", "en-EN")
 

Knowing this, you can even generate a calendar that adjusts to the user’s language. You could for example have a parameter with the desired language, and adjust the culture codes accordingly. If you take it a step further you could even change the column names to reflect the selected language. This requires some more attention and is a topic worth a separate blogpost.

Another common column to add to your calendar is the Year-Month column. And you can use different formats here. Some examples are:

= Date.ToText( [Dates], "yyyy-MM")  // Adds YYYY-MM (2021-01 - 2021-12) 
     
= Date.ToText( [Dates], "MMM yyyy") // Adds MM YYYY (Jan 2021 - Dec 2021) 

= Date.ToText( [Dates], "yyyyMM")   // Adds MM YYYY (202101 - 202112) 


For quarter columns in your date table, the below formatting is common.

     // Adds Quarter Number (Q1 - Q4)
"Q" & Text.From( Date.QuarterOfYear([Dates]) ) 

     // Adds Quarter Number (2021-Q1 - 2021-Q4)
= Text.From( Date.Year([Dates]) ) & 
    "-Q" & 
      Text.From( Date.QuarterOfYear([Dates]) ) 

Other columns frequently included in the date dimension indicate whether a day is in the weekend or the weekday. One way to write this is:

     // Adds flag: is Weekend
= if Date.DayOfWeek([Dates]) >= 5 then 1 else 0

     // Adds flag: is Weekday
= if Date.DayOfWeek([Dates]) < 5 then 1 else 0

4. Creating Date Table with Script

So far we have seen a wide range of possible columns. It’s likely you only need a selection or have some other wishes. To help you set up the foundation, you can copy below script to create your date table in Power Query. Starting from this template, you can easily adjust it to fit your needs.

let
    Source = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) +1, #duration( 1, 0, 0, 0 ) ),
    #"Converted to Table" = Table.FromList(
    Source, 
    Splitter.SplitByNothing(), 
    { "Dates" }, 
    null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Dates", type date}}),
    #"Insert Age" = Table.AddColumn(#"Changed Type", "Age", each Date.From(DateTime.LocalNow()) - [Dates], type duration),
    #"Insert Year" = Table.AddColumn(#"Insert Age", "Year", each Date.Year([Dates]), Int64.Type),
    #"Insert Start of Year" = Table.AddColumn(#"Insert Year", "Start of Year", each Date.StartOfYear([Dates]), type date),
    #"Insert End of Year" = Table.AddColumn(#"Insert Start of Year", "End of Year", each Date.EndOfYear([Dates]), type date),
    #"Insert YYYY-MM" = Table.AddColumn(#"Insert End of Year", "YYYY-MM", each Date.ToText( [Dates], "yyyy-MM"), type text),
    #"Insert MonthSh-Year" = Table.AddColumn(#"Insert YYYY-MM", "Month-Year", each Date.ToText( [Dates], "MMM yyyy"), type text),
    #"Insert YearMonth" = Table.AddColumn(#"Insert MonthSh-Year", "YearMonth", each Date.ToText( [Dates], "yyyyMM"), type text),
    #"Insert Month" = Table.AddColumn(#"Insert YearMonth", "Month", each Date.Month([Dates]), Int64.Type),
    #"Insert Start of Month" = Table.AddColumn(#"Insert Month", "Start of Month", each Date.StartOfMonth([Dates]), type date),
    #"Insert Days in Month" = Table.AddColumn(#"Insert Start of Month", "Days in Month", each Date.DaysInMonth([Dates]), Int64.Type),
    #"Insert Month Name" = Table.AddColumn(#"Insert Days in Month", "Month Name", each Date.MonthName([Dates]), type text),
    #"Insert Month Name Short" = Table.AddColumn(#"Insert Month Name", "Month Name Short", each Date.ToText( [Dates] , "MMM" ), type text),
    #"Insert Month Name Short NL" = Table.AddColumn(#"Insert Month Name Short", "Month Name Short (NL)", each Date.ToText( [Dates] , "MMM", "nl-NL" ), type text),
    #"Insert Quarter Number" = Table.AddColumn(#"Insert Month Name Short NL", "Quarter Number", each Date.QuarterOfYear([Dates]), Int64.Type),
    #"Insert Start of Quarter" = Table.AddColumn(#"Insert Quarter Number", "Start of Quarter", each Date.StartOfQuarter([Dates]), type date),
    #"Added Quarter" = Table.AddColumn(#"Insert Start of Quarter", "Quarter", each "Q" & Text.From( Date.QuarterOfYear([Dates]) ), type text ),
    #"Add Year-Quarter" = Table.AddColumn(#"Added Quarter", "Year-Quarter", each Text.From( Date.Year([Dates]) ) &  "-Q" &  Text.From( Date.QuarterOfYear([Dates]) ), type text ),
    #"Insert Week of Year" = Table.AddColumn(#"Add Year-Quarter", "Week of Year", each Date.WeekOfYear([Dates]), Int64.Type),
    #"Insert Week of Month" = Table.AddColumn(#"Insert Week of Year", "Week of Month", each Date.WeekOfMonth([Dates]), Int64.Type),
    #"Insert Start of Week" = Table.AddColumn(#"Insert Week of Month", "Start of Week", each Date.StartOfWeek([Dates]), type date),
    #"Insert End of Week" = Table.AddColumn(#"Insert Start of Week", "End of Week", each Date.EndOfWeek([Dates]), type date),
    #"Insert Day" = Table.AddColumn(#"Insert End of Week", "Day", each Date.Day([Dates]), Int64.Type),
    #"Insert Day of Week" = Table.AddColumn(#"Insert Day", "Day of Week", each Date.DayOfWeek([Dates]), Int64.Type),
    #"Insert Day of Year" = Table.AddColumn(#"Insert Day of Week", "Day of Year", each Date.DayOfYear([Dates]), Int64.Type),
    #"Insert Start of Day" = Table.AddColumn(#"Insert Day of Year", "Start of Day", each Date.StartOfDay([Dates]), type date),
    #"Insert End of Day" = Table.AddColumn(#"Insert Start of Day", "End of Day", each Date.EndOfDay([Dates]), type date),
    #"Insert Day Name" = Table.AddColumn(#"Insert End of Day", "Day Name", each Date.DayOfWeekName([Dates]), type text),
    #"Insert Is Weekend" = Table.AddColumn(#"Insert Day Name", "Is Weekend", each if Date.DayOfWeek([Dates]) >= 5 then 1 else 0, Int64.Type ),
    #"Insert Is Weekday" = Table.AddColumn(#"Insert Is Weekend", "Is Weekday", each if Date.DayOfWeek([Dates]) < 5  then 1 else 0, Int64.Type )
in
    #"Insert Is Weekday"

5. Creating Dynamic Date Table

So far we have used parameters to determine both the start and end date of the calendar. For some this meets their needs. Yet there are cases where you only want to see dates that meet a certain criteria. This chapter shows two ways on how to make the calendar table truly dynamic.

5.1. Dates up to Today

Earlier we used the Manage Parameters box to add a hardcoded end date. Instead of having this fixed end date, you can easily end your calendar on the date of today. If this does not bring any complications with future dates (e.g. budgets, forecast etc), you can replace the parameter by the date of today in the following way.

Start by creating a New Query, select Blank Query. Your goal is to retrieve the current date. Power Query knows the function DateTime.LocalNow that retrieves the current DateTime value.

= DateTime.LocalNow() 

Since we need a Date value, you can adjust this formula to:

= Date.From( 
     DateTime.LocalNow() 
 )

The result of the formula is the current Date on your machine. You can rename this query to DateToday. Now instead of referencing the EndDate parameter, you replace that value with DateToday. With this adjustment your Calendar will always run up to today.

5.2. Dates up to Max Sales Date

Another common approach is maximize the calendar to the data with the latest sales. This approach is very similar to the previous one. Imagine you have a table called Sales that contains a column with OrderDate. To create your MaxSalesDate parameter, you first create a blank query and rename it to MaxSalesDate. Next, you retrieve the maximum date from the Sales table.

Referencing a column name in a table results in a list. To get the latest sales date we can therefore use the List.Max function:

= List.Max( Sales[Date] )

With this parameter in place, you can now reference the MaxSalesDate instead of the EndDate Parameter.

This post was a guide to create a dynamic date table. Showing you how to create a series of dates, how to add columns both through the user interface and manually, and showing you how to make the calendar dynamic. As the post shows having knowledge of list functions makes your life much easier.

Hopefully this was helpful. Are there any topics missing or that you want more information on? Let me know in the comments.

Enjoy Power Query!

About Rick de Groot

Rick is the founder and editor of BI Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

1 thought on “Create Date Table or Calendar in Power Query M | including Script”

Leave a comment