Create Date Table or Calendar in Power Query M | with Free 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.
0.5 Manage Parameters

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

0. Two parameters for calendar

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
1. Create a list of dates with List.Dates

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
     #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(
     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(

To save yourself the step of renaming the new column (Column1) and defining the data type later, you can make an adjustment to the previous formula. You can do this by defining the table syntax in argument. Lastly, argument 4 and 5 are optional, you can remove them.

= Table.FromList(
     type table[ Date = Date.Type ], // Adds Column Name and Data Type
3.5 Change data type and rename

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 ) }
3. Autofill dates in list

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.

4. Add Date Columns

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 )
// 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" )  // (2021-01 - 2021-12) 
= Date.ToText( [Dates], "MMM yyyy" ) // (Jan 2021 - Dec 2021) 

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

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

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

= Text.From( Date.Year( [Dates] ) ) &  // (2021-Q1 - 2021-Q4)
    "-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.

    Today = Date.From( DateTime.LocalNow() ),
    StartDate = #date(2022, 1, 1),
    EndDate = Date.EndOfYear( Today ),
    #"List of Dates" = List.Dates( StartDate, Duration.Days( EndDate - StartDate ) +1, #duration( 1, 0, 0, 0 ) ),
    #"Converted to Table" = Table.FromList( #"List of Dates", Splitter.SplitByNothing(), type table[Date = Date.Type] ),
    #"Insert Date Integer" = Table.AddColumn(#"Converted to Table", "Date Integer", each Number.From( Date.ToText( [Date], "yyyyMMdd" ) ), Int64.Type ),
    #"Insert Year" = Table.AddColumn(#"Insert Date Integer", "Year", each Date.Year([Date]), Int64.Type),
    // Creates a dynamic year value called 'Current' that moves with the current date. Put this value in a slicer and it automatically switches to the Current period.
    #"Add Year Default" = Table.AddColumn(#"Insert Year", "Year Default", each if Date.Year( Today ) = [Year] then "Current" else Text.From( [Year] ), type text),
    #"Insert YYYY-MM" = Table.AddColumn(#"Add Year Default", "YYYY-MM", each Date.ToText( [Date], "yyyy-MM"), type text),
    #"Insert Month-Year" = Table.AddColumn(#"Insert YYYY-MM", "Month-Year", each Date.ToText( [Date], "MMM yyyy"), type text),
    #"Insert Month Number" = Table.AddColumn(#"Insert Month-Year", "Month Of Year", each Date.Month([Date]), Int64.Type),
    #"Insert Month Name" = Table.AddColumn(#"Insert Month Number", "Month Name", each Date.MonthName([Date], "EN-us"), type text),
    #"Insert Month Name Short" = Table.AddColumn(#"Insert Month Name", "Month Name Short", each Date.ToText( [Date] , "MMM", "EN-us" ), type text),
    // Creates a dynamic year value called 'Current' that moves with the current date. Put this value in a slicer and it automatically switches to the current period.
    #"Add Month Name Default" = Table.AddColumn(#"Insert Month Name Short", "Month Name Default", each if Date.Month( Today ) = [Month Of Year] then "Current" else [Month Name], type text ),
    #"Insert Start of Month" = Table.AddColumn(#"Add Month Name Default", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Insert Start of Month", "End of Month", each Date.EndOfMonth( [Date] ), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Add ISO Week" = Table.AddColumn(#"Inserted Days in Month", "ISO Weeknumber", each let
CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday ) ),
YearCurrThursday = Date.Year( CurrentThursday ),
FirstThursdayOfYear = Date.AddDays(#date( YearCurrThursday,1,7),- Date.DayOfWeek(#date(YearCurrThursday,1,1), Day.Friday) ),
ISO_Week = Duration.Days( CurrentThursday - FirstThursdayOfYear) / 7 + 1
in ISO_Week, Int64.Type ),
    #"Add ISO Year" = Table.AddColumn(#"Add ISO Week", "ISO Year", each let
CurrentThursday = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday ) ),
YearCurrThursday = Date.Year( CurrentThursday )
in YearCurrThursday, Int64.Type ),
    #"Insert Start of Week" = Table.AddColumn(#"Add ISO Year", "Start of Week", each Date.StartOfWeek([Date], Day.Monday ), type date),
    #"Insert Quarter Number" = Table.AddColumn(#"Insert Start of Week", "Quarter Number", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Quarter" = Table.AddColumn(#"Insert Quarter Number", "Quarter", each "Q" & Text.From( Date.QuarterOfYear([Date]) ), type text ),
    #"Add Year-Quarter" = Table.AddColumn(#"Added Quarter", "Year-Quarter", each Text.From( Date.Year([Date]) ) & "-Q" & Text.From( Date.QuarterOfYear([Date]) ), type text ),
    #"Insert Day Name" = Table.AddColumn(#"Add Year-Quarter", "Day Name", each Date.DayOfWeekName([Date], "EN-us" ), type text),
    #"Insert Day Name Short" = Table.AddColumn( #"Insert Day Name", "Day Name Short", each Date.ToText( [Date], "ddd", "EN-us" ), type text),
    #"Insert Day of Month Number" = Table.AddColumn(#"Insert Day Name Short", "Day of Month Number", each Date.Day([Date]), Int64.Type),
    #"Insert Day of Week" = Table.AddColumn(#"Insert Day of Month Number", "Day of Week Number", each Date.DayOfWeek([Date]), Int64.Type),
    #"Insert Day of Year" = Table.AddColumn(#"Insert Day of Week", "Day of Year Number", each Date.DayOfYear([Date]), Int64.Type),
    #"Add Day Offset" = Table.AddColumn(#"Insert Day of Year", "Day Offset", each Number.From( Date.From( Today ) - [Date] ) , Int64.Type ),
    #"Add Week Offset" = Table.AddColumn(#"Add Day Offset", "Week Offset", each ( Number.From( Date.StartOfWeek( [Date], Day.Monday) )- Number.From( Date.StartOfWeek( Today, Day.Monday ) ) ) / 7, Int64.Type ),
    #"Add Month Offset" = Table.AddColumn(#"Add Week Offset", "Month Offset", each ([Year] - Date.Year( Today ) ) *12 + ( [Month Of Year] - Date.Month( Today ) ), Int64.Type ),
    #"Add Quarter Offset" = Table.AddColumn(#"Add Month Offset", "Quarter Offset", each ( [Year] - Date.Year(Today) ) * 4 + Date.QuarterOfYear( [Date] ) - Date.QuarterOfYear( Today ), Int64.Type ),
    #"Add Year Offset" = Table.AddColumn(#"Add Quarter Offset", "Year Offset", each [Year] - Date.Year(Today), Int64.Type ),
    #"Insert Is Weekend" = Table.AddColumn(#"Add Year Offset", "Is Weekend", each if Date.DayOfWeek([Date]) >= 5 then 1 else 0, Int64.Type ),
    #"Insert Is Weekday" = Table.AddColumn(#"Insert Is Weekend", "Is Weekday", each if Date.DayOfWeek([Date]) < 5  then 1 else 0, Int64.Type )
    #"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( 

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.

If you want to make your calendar even more dynamic, you can make sure it has a flexible calendar language for column names and values.

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!

Recommend Reading

>>> Power Query - Foundations <<<
 List Functions (200+ examples)
 Text functions (150+ examples)
Creating Tables from Scratch (40+ examples)
 Understanding If Statements 
Generating a Date Table

>>>  Power Query - Advanced Topics <<<
Master List.Generate with Easy Examples
 Deep Dive into List.Accumulate
Create Calendar with Dynamic Language
Replacing Values (User Interface and Beyond)
Share on:
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.

9 thoughts on “Create Date Table or Calendar in Power Query M | with Free Script”

  1. Hi, you have created two such solutions:
    “ISO Week”
    “ISO Year”
    But what about: “ISO DAY OF WEEK”? Something like WEEKDAY in DAX? Where I can choose to count the days of the week from 1 to 7. And one is Monday.
    Would you help here?

  2. Hello Rick,

    Thanks for this well explained and overall complete post. Most blogs give you only one proposed solution for a specific problem.

    I created a calender table this way:

    Startdate = #date(2020,1,1),
    Enddate = #date(2021,4,30),
    #"Date list" = List.Generate(
    () => Number.From(Startdate),
    each _ <= Number.From(Enddate),
    each _ + 1),
    #"List to table" = Table.FromList(#"Date list", Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"List to table",{{"Date", type date}}),
    #"Duplicate columns" = Table.DuplicateColumn(
    Table.DuplicateColumn(#"Changed Type", "Date", "Year"),
    "Date", "Quarter"),
    "Date", "Month"),
    "Date", "Day"),
    "Date", "Weeknumber"),
    "Date", "Monthname"),
    "Date", "Dayname"),
    #"Full table creation" = Table.TransformColumns(#"Duplicate columns", {{"Year", Date.Year}, {"Quarter", Date.QuarterOfYear}, {"Month", Date.Month}, {"Day", Date.Day}, {"Weeknumber", Date.WeekOfYear}, {"Monthname", Date.MonthName}, {"Dayname", Date.DayOfWeekName}})
    #"Full table creation"

    But in the last line where I do Table.TransformColumns I can’t manage to write the short month name. I have tried with Date.ToText( [Date] , “MMM”, “EN-us” ) but PQ doesn’t accept the [Date]. Any ideas?

    • Hi Stijn,

      For your short monthname you can use:

      {"Dayname", each Date.ToText(_, "MMM") }

      Just make sure to add the word ‘each’ before the statement, and the _ to reference the column.

  3. One comment – and One question

    I’m using for generating Date Table something like:

        Columns = {"Year", "Month", "Day", "MonthName", "KuuNimi"},
        Allikas = List.Dates( #date(2022,1,1), 365, #duration( 1, 0, 0, 0 ) ),
        #"Teisendatud tabeliks" = Table.FromList(Allikas, Splitter.SplitByNothing(), type table[Date = Date.Type ], null, ExtraValues.Error),
        #"Lisatud kohandatud" = Table.AddColumn(#"Teisendatud tabeliks", "Kohandatud", each Record.FromList(
    Date.ToText([Date], "MMMM", "en-us"),
    Date.ToText([Date], "MMMM", "et-ee")
        #"Laiendatud Kohandatud" = Table.ExpandRecordColumn(#"Lisatud kohandatud", "Kohandatud", Columns, Columns),
        #"Muudetud tüüp" = Table.TransformColumnTypes(#"Laiendatud Kohandatud",{{"Month", Int64.Type}, {"Day", Int64.Type}, {"MonthName", type text}, {"KuuNimi", type text}})
        #"Muudetud tüüp"

    So I simply minimize the steps in my Query and create several columns once

    BUT – do You know any way to expand record with exact type to precvent TransformColumnTypes

  4. Pingback: Import Multiple Files from a Folder with Power Query - Gorilla BI
  5. Pingback: Create Date Table with Dynamic Language in Power Query - Gorilla BI
  6. I had to go “MM” on the step

    = Table.AddColumn(
      #"Converted to table", 
      "Date Integer", 
      each Date.ToText([Date], "yyyyMMdd"), 

    But maybe thats just my regional settings. mm was minutes (gave error) for me.

    • Helge. For me it didn’t throw an error, but the dateinteger just showed 00 for month. I changed it to MM in line with what you suggested.

      Thanks for pointing that out!

  7. Pingback: Lists in Power Query - The Complete Guide - Gorilla BI

Leave a comment