Generating A Date Dimension Table In Power Query

There are hundreds of different methods for generating a Date dimension table for use in Power Pivot: you can use TSQL, Excel formulas, import from the DateStream table in the Windows Azure Marketplace, and there’s even an app for it. Nevertheless I thought it would be a good idea to solve this problem in Power Query because:

  • It seems like the natural tool to use, and a date table is one of the first tables a data steward will want to share with his/her users.
  • It’s a very good way to learn about and demonstrate all the date functionality in M and Power Query. The more M code examples that there are out there, the better, right?
  • Having just signed the contract to write a whole book on Power Query I need to master M very quickly!

Here’s how I did it. First of all, I decided to implement a function to create the table rather than a regular query; this function takes two parameters: the start date and the end date of the range of dates to appear in the table. Here’s the function I came up with:


    CreateDateTable = (StartDate, EndDate) =>




    //Create lists of month and day names for use later on

    MonthList = {"January", "February", "March", "April", "May", "June"

                 , "July", "August", "September", "October", "November", "December"},

    DayList = {"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"},

    //Find the number of days between the end date and the start date

    NumberOfDates = Duration.Days(EndDate-StartDate)+1,

    //Generate a continuous list of dates from the start date to the end date

    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

    //Turn this list into a table

    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}

                     , null, ExtraValues.Error),

    //Caste the single column in the table to type date

    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),

    //Add custom columns for day of month, month number, year

    DayOfMonth = Table.AddColumn(ChangedType, "DayOfMonth", each Date.Day([Date])),

    MonthNumber = Table.AddColumn(DayOfMonth, "MonthNumberOfYear", each Date.Month([Date])),

    Year = Table.AddColumn(MonthNumber, "Year", each Date.Year([Date])),

    DayOfWeekNumber = Table.AddColumn(Year, "DayOfWeekNumber", each Date.DayOfWeek([Date])+1),

    //Since Power Query doesn't have functions to return day or month names, 

    //use the lists created earlier for this

    MonthName = Table.AddColumn(DayOfWeekNumber, "MonthName", each MonthList{[MonthNumberOfYear]-1}),

    DayName = Table.AddColumn(MonthName, "DayName", each DayList{[DayOfWeekNumber]-1}),

    //Add a column that returns true if the date on rows is the current date

    IsToday = Table.AddColumn(DayName, "IsToday", each Date.IsInCurrentDay([Date]))






Some things to point out:

  • Look – comments! Both multi-line /* */ and single line //. Adding comments to complex Power Query queries and functions is of course a Good Thing and something we should all be doing.
  • The way I created the table from the start and end date is quite interesting: the List.Dates() function allows you to generate a list of dates from one date to another; I was then able to take this list and turn it into a table with Table.FromList().
  • Adding extra columns to the table for the year and month number and so on is fairly straightforward. However, as far as I can see, Power Query does not have the ability to return month or day names (as opposed to numbers) from a date, so to get round this I created two lists called MonthList and DayList containing the months of the year and the days of the week, in order, took the day and month numbers and used these to find the name at the nth position in the list (see the MonthName and DayName steps).
  • Date.IsInCurrentDay() compares a date with the current system date and allows you to see if a date is today’s date. There are a whole bunch of other interesting functions like this such as Date.IsInPreviousWeek(), Date.IsInCurrentYear() and so on, useful for creating relative date dimension tables like the one I describe here.

With the function created it’s very easy to generate a table using it. For example, I created a parameter table with start date and end date columns like so:


I then used this table as the starting point for a new query that called my function:


    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

    ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", type date}

                  , {"End Date", type date}}),

    CallCreateDateTable = CreateDateTable(ChangedType[Start Date]{0}

                          ,ChangedType[End Date]{0})



Here’s the output:


You can download my demo workbook with all the code in from here.

PS If you haven’t downloaded the November update for Power Query already, I recommend you do so – the UI has been improved a lot.

51 responses

  1. Did you intend to leave a link in your last bullet?

    Also, Chris, do you have any resources about the full list of file properties/attributes that can be queried via PowerQuery?
    In particular, I am currently running a query that returns a list of files in a folder (directory) and a number of properties related to those files, such as Date Created, Date Modified, File Size, etc.
    I would like to retrieve the “Last Modified By” or “Owner” property of those files. Any help would be much appreciated.

  2. Hi Chris

    Great news that you are writing a Powerquery book – I can’t wait.

    I have also looked at creating a date dimension that looks similar to yours. I have also combined it with an external calendar website called to automatically append UK holidays to it. Here is my code – inevitably not as well written as yours

    let paramquery = (mystartdate as date, myenddate as date) =>
    mydays = Number.From(myenddate) – Number.From(mystartdate) + 1 ,
    myDates = List.Dates(mystartdate, mydays, Duration.From(1)),
    TableFromList = Table.FromList(myDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ChangedType1 = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType1,{{“Column1”, “FullDate”}}),
    DayNumber = Table.AddColumn( RenamedColumns ,” DayNumber “,each Date.Day([FullDate])),
    DayName = Table.AddColumn(DayNumber, “#(tab)DayName#(tab)”, each Date.ToText([FullDate],” dddd “)),
    DayAbbrev = Table.AddColumn( DayName ,” DayAbbrev “,each Date.ToText([FullDate],” ddd “)),
    MonthNumber = Table.AddColumn( DayAbbrev ,” MonthNumber “,each Date.Month([FullDate])),
    MonthName = Table.AddColumn( MonthNumber ,” MonthName “,each Date.ToText([FullDate],” MMMM “)),
    MonthAbbrev = Table.AddColumn( MonthName ,” MonthAbbrev “,each Date.ToText([FullDate],” MMM “)),
    YearNumber = Table.AddColumn( MonthAbbrev ,” YearNumber “,each Date.Year([FullDate])),
    DateKey = Table.AddColumn( YearNumber ,” DateKey “,each Date.ToText([FullDate],” ddmmyyyy “)),
    DayofWeek = Table.AddColumn( DateKey ,” DayOfweek “,each Date.DayOfWeek([FullDate],1)+1),
    DayofYear = Table.AddColumn( DayofWeek ,” DayofYear “,each Date.DayOfYear([FullDate])),
    DaysInMonth = Table.AddColumn( DayofYear ,” DaysInMonth “,each Date.DaysInMonth([FullDate])),
    WeekOfMonth = Table.AddColumn( DaysInMonth ,” WeekOfMonth “,each Date.WeekOfMonth([FullDate])),
    WeekOfYear = Table.AddColumn( WeekOfMonth ,” WeekOfYear “,each Date.WeekOfYear([FullDate])),
    ReorderedColumns = Table.ReorderColumns(WeekOfYear,{“FullDate”, “#(tab)DateKey#(tab)”, “#(tab)DayNumber#(tab)”, “#(tab)DayName#(tab)”, “#(tab)DayAbbrev#(tab)”, “#(tab)MonthNumber#(tab)”, “#(tab)MonthName#(tab)”, “#(tab)MonthAbbrev#(tab)”, “#(tab)YearNumber#(tab)”, “#(tab)DayOfweek#(tab)”, “#(tab)DayofYear#(tab)”, “#(tab)DaysInMonth#(tab)”, “#(tab)WeekOfMonth#(tab)”, “#(tab)WeekOfYear#(tab)”}),
    GroupedRows = Table.Group(ReorderedColumns, {“#(tab)YearNumber#(tab)”}, {{“Count”, each Table.RowCount(_), type number}}),
    ChangedType2 = Table.TransformColumnTypes(GroupedRows,{{“#(tab)YearNumber#(tab)”, type text}}),
    InsertedCustom = Table.AddColumn(ChangedType2, “Custom”, each DateHolidays([#”#(tab)YearNumber#(tab)”])),
    RenamedColumns2 = Table.RenameColumns(InsertedCustom ,{{“#(tab)YearNumber#(tab)”, “YearNumber2″}}),
    #”Expand Custom” = Table.ExpandTableColumn(RenamedColumns2, “Custom”, {“Holiday name”, “Holiday type”, “Where it is observed”, “Custom”}, {“Custom.Holiday name”, “Custom.Holiday type”, “Custom.Where it is observed”, “Custom.Custom”}),
    EndTable = Table.Join(ReorderedColumns, “FullDate”, #”Expand Custom”, “Custom.Custom”, JoinKind.LeftOuter),
    SortedRows = Table.Sort(EndTable,{{“FullDate”, Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(SortedRows,{“YearNumber2”, “Count”, “Custom.Custom”}),
    RenamedColumns1 = Table.RenameColumns(RemovedColumns,{{“Custom.Holiday name”, “Holiday name”}, {“Custom.Holiday type”, “Holiday type”}, {“Custom.Where it is observed”, “Where it is observed”}})


  3. Chris

    Me again

    You do not have to hard code names of month and days in list because you can use this

    MonthName= Table.AddColumn(MonthNumber,”MonthName”,each Date.ToText([FullDate],”MMMM “)),

    or this
    DayName= Table.AddColumn(DayNumber, “#(tab)DayName#(tab)”, each Date.ToText([FullDate],”dddd”)),
    DayAbbrev= Table.AddColumn(DayName,”DayAbbrev “,each Date.ToText([FullDate],”ddd “)),

  4. Pingback: Generating A Date Dimension Table In Power Query | MS Excel | Power Pivot | DAX

  5. Just been working with a client on calling stored procedures and passing required parameters using Powerquery very easy very powerful. Syntax is same as you would use in management studio ie

    EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;

    you can change value of BusinessEntityID using PQ function capability

    • Typically far simpler than I expected, and why is it like TSQL which is for me would have been the best solution, so least likely for me to guess at!
      ‘fraid I got frustrated and used the less friendly world of VBA so solve it this time, but thanks very much for the info kind sir.

  6. Dear Chris,
    Thank you for your usefull post. Where have you defined the “CreateDateTable” function in the excel workbook? I can’t find the function defininition anywhere.
    Thank you!

    • You need to have the latest version of Power Query installed – then go to the Power Query tab, click on the Workbook button in the Manage Queries section, and in the workbook queries pane you’ll see a query (in fact a function) called CreateDateTable.

      • Thank you very much Chiris. It was my fault. I haven’t the the latest version of PQ installed.

  7. Hi Chris,

    Thanks for posting this, I’ve created my own dimDate table based on this, but I can’t figure out how to get IsInCurrentWeek & IsInPreviousWeek Logical TRUE/FALSE values based off of week that starts on Monday and ends in Sunday. I’ve created my own logic for it but it breaks down at the beginning and end of the year. What else can I do?

    // Choose Start & End Date

    // Calculate number of dates
    NumberOfDates = Duration.Days(EndDate-StartDate),

    //Generate a continuous list of dates from the start date to the end date
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0)),

    //Turn this list into a table
    TableFromList = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}
    , null, ExtraValues.Error),

    //Caste the single column in the table to type date
    ChangedType = Table.TransformColumnTypes(TableFromList,{{“Date”, type date}}),

    //Add custom columns for day of month, month number, year, etc etc
    Day = Table.AddColumn(ChangedType, “Day”, each Date.Day([Date])),
    DayDoubleDigit = Table.AddColumn(Day, “DayDoubleDigit”, each Date.ToText([Date],”dd”)),

    MonthNumber = Table.AddColumn(DayDoubleDigit, “MonthNumber”, each Date.Month([Date])),
    MonthName = Table.AddColumn(MonthNumber, “MonthName”, each Date.ToText([Date],”MMMM”)),
    MonthNameShort = Table.AddColumn(MonthName, “MonthNameShort”, each Date.ToText([Date],”MMM”)),

    Year = Table.AddColumn(MonthNameShort, “Year”, each Date.Year([Date])),

    DayOfWeekNumber = Table.AddColumn(Year, “DayOfWeek”, each Date.DayOfWeek([Date])+1),
    DayName = Table.AddColumn(DayOfWeekNumber, “DayName”, each Date.ToText([Date],”dddd”)),
    DayNameShort = Table.AddColumn(DayName, “DayNameShort”, each Date.ToText([Date],”ddd”)),

    IsToday = Table.AddColumn(DayNameShort, “IsToday”, each Date.IsInCurrentDay([Date])),
    IsInCurrentWeek = Table.AddColumn(IsToday,”IsInCurrentWeek”,each Date.IsInCurrentWeek(Date.AddDays([Date],-1))),
    IsInCurrentMonth= Table.AddColumn(IsInCurrentWeek, “IsInCurrentMonth”, each Date.IsInCurrentMonth([Date])),
    IsInCurrentYear= Table.AddColumn(IsInCurrentMonth, “IsInCurrentYear”, each Date.IsInCurrentYear([Date])),

    IsInPreviousWeek = Table.AddColumn(IsInCurrentYear,”IsInPreviousWeek”,each Date.IsInPreviousWeek(Date.AddDays([Date],-1))),
    IsInPreviousMonth= Table.AddColumn(IsInPreviousWeek, “IsInPreviousMonth”, each Date.IsInPreviousMonth([Date])),
    IsInPreviousYear= Table.AddColumn(IsInPreviousMonth, “IsInPreviousYear”, each Date.IsInPreviousYear([Date])),

    SemiMonthDaySet = Table.AddColumn(IsInPreviousYear, “SemiMonthDaySet”, each (if [Day]<16 then "1-15" else Text.Combine({Text.From(16),Text.From(Date.Day(Date.EndOfMonth([Date])))},"-"))),

    SemiMonthCurrentDaySet = Table.AddColumn(SemiMonthDaySet, "SemiMonthCurrentDaySet", each if Date.Day(DateTime.LocalNow()) < 16
    then (if Date.IsInCurrentMonth([Date]) = Logical.FromText("True")
    then (if Date.Day([Date]) 15
    then “TRUE”
    else “FALSE”)
    else “FALSE”)),

    SemiMonthPreviousDaySet = Table.AddColumn(SemiMonthCurrentDaySet, “SemiMonthPreviousDaySet”, each if Date.Day(DateTime.LocalNow()) 15
    then “TRUE”
    else “FALSE”)
    else “FALSE”)
    else (if Date.IsInCurrentMonth([Date]) = Logical.FromText(“True”)
    then (if Date.Day([Date]) < 16
    then "TRUE"
    else "FALSE")
    else "FALSE")),

    QuarterOfYear= Table.AddColumn(SemiMonthPreviousDaySet, "QuarterOfYear",each Text.Combine({"Q", Text.From(Date.QuarterOfYear([Date]))}," ")),

    WeekOfYear = Table.AddColumn(QuarterOfYear,"WeekOfYear",each Date.WeekOfYear(Date.AddDays([Date],-1))),
    WeekNameOfYear =Table.AddColumn(WeekOfYear ,"WeekNameOfYear ",each Text.Combine({"Week",Text.PadStart(Text.From([WeekOfYear]),2,"0")}," "))


      • I want the IsInCurrentWeek to start the week on Monday (like DAX function WEEKNUM(, 2)). Right now I’m calculating it like this in powerquery, Date.IsInCurrentWeek(Date.AddDays([Date],-1))), but the first day of the every year it shows like it’s last week of last year (week 53). Does the IsInCurrentWeek() have any return_type parameter like the DAX fucntion (So something like IsIncurrentWeek(,2)), I tried searching but couldn’t find any.

      • Could you hack it by putting in a test that says if the current week number you’re calculating is 53 and the date is January 1st, then the actual week number is 1 (otherwise return the original week number you are calculating)?

    • Thanks Chris, I did it the following way,

      each if [Date] = Date.StartOfYear([Date]) then Date.IsInCurrentWeek([Date]) else Date.IsInCurrentWeek(Date.AddDays([Date],-1))),

  8. Pingback: Creating a Date Dimension with a Power Query Script

  9. Pingback: Allocation in Power Query, Part 2 | Chris Webb's BI Blog

  10. Pingback: Allocation in Power Query, Part 2 - SQL Server - SQL Server - Toad World

  11. Pingback: Dynamic Write-Back to Sync PowerPivot Tables with Date Tables created by Power Query | Excel For You

  12. Hi Chris,

    I’ve posted this issue in powerquey forum already (link below) but I just wanted to ask if you’ve come across this. i’m using this DimDate table in many of the reports but after a recent update I’ve been getting out of line object error in many of them. Do you have any ideas what could be causing it. The screenshot and code is on the link below too.

      • Others seem to work fine… the query works fine by itself.. but when it’s refreshed along with all other connections (powerpivot connections to an ODBC database) in a workbook.. it fails to refresh and gives the error..

  13. Pingback: It’s Just a Matter of Time: Power BI Date & Time Dimension Toolkit | Gina Meronek

  14. Hi Chris,

    Just one minor adjustment to the query, I think the
    DateList = List.Dates(StartDate, NumberOfDates, #duration(1, 0, 0, 0))
    should be
    DateList = List.Dates(StartDate, NumberOfDates+1, #duration(1, 0, 0, 0))
    otherwise, it misses the last day of the year.

  15. Pingback: Kasper de Jonge PowerPivot Blog | Automatically ranged date table using DAX and M

  16. Pingback: Automatically ranged date table using DAX and M | Business Intelligence Info

  17. Pingback: Use Power Query to Generate Date Table with Australian Financial Year and Holiday Flag | George's BI Blog

  18. Pingback: PowerQuery DateDimension query | Rui Romano Blog

  19. Pingback: Depreciation Pattern for SSAS Tabular and Power Pivot Data Models

  20. Pingback: Creating a Date Dimension with Power Query | Devin Knight

  21. Pingback: Automatically Generating Date Dimension Tables In Excel 2016 Power Pivot | Chris Webb's BI Blog

  22. Pingback: Automatically Generating Date Dimension Tables In Excel 2016 Power Pivot - SQL Server - SQL Server - Toad World

  23. I have a table on an excel worksheet which includes a date column and has the cell values in 2 different visually represented formats; however, all the values in the date column in excel are set to “general”.

    No cell value is formatted as “date” nor “number” in excel within the date column.

    In the table, the dates are either displayed as 41425 or 31/05/2013. Both represent the same exact date.

    When I import into Power Query and select the date column, it is automatically formatted as Data Type: Text.

    If I select the entire column and select “Date” in Data Type drop down, the entire column errors out.

    If I select the entire column, right click and go to Change Type > using locale, the entire column errors out.

    How can I get the cell values in the Date column formatted to MM/DD/YYYY format in Power Query without altering the format/values in the data table which holds the raw data from our private server?

    Thank you in advance!

    • I think you need to split the table into two inside Power Query, each table containing one of the two formats, then in each convert the dates and then finally combine the two tables back into one.

  24. Pingback: Power BI and Google Analytics | Another SQL Geek

  25. Pingback: Date Dimension in power Pivot 2016 | Power Blogger BI

  26. Pingback: Power Query is awesome – dynamic table of dates – #powerbi | Erik Svensen

  27. Pingback: Power Query: Week or Weak | Karl Beran's BI mumble

  28. Pingback: Every Power BI model needs a date entity – did i say that?!

  29. Pingback: Power BI Date Tables: A Complete How-to Guide to Create & Use Date Tables

Leave a Reply to Bellicose Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: