Creating a Date Dimension with Power Query

Devin-Knight, 2015-06-16

A date dimension or table can be extremely important when working on a Power BI project, or BI projects in general for that mater. Here’s some of the quick benefits and reasons why you need a date table:

  • Helpful when filtering data
    • Filter by year, quarter, month, etc…
  • Helpful for drilling into a hierarchy of dates
    • Expand 2015 to see all the months within that year
    • Drill in to January to see all the days within that month
  • It’s required to do Time Intelligence functions in Power Pivot with DAX
    • Calculating year to date, prior period, etc…
  • It allows you to track important dates like holidays in a central spot.
    • Theses can be applied to filters
  • Allows you to track multiple types of dates
    • Calendar, fiscal, manufacturing, etc…

Just about any project will likely require one or many of these features. So if you’re working on a Power BI project what are your options?

  1. Import a date table that exist in your source already and use it
  2. Import a date table from the Azure Marketplace (I’ve used DateStream in the past successfully)
  3. Use Power Query and the M query language to create your own date table from scratch

For this post I’d like to share with you how to do option 3 using a script that I’ve created to generate a date dimension. Here’s the steps to replicate this table on your own.

Creating the Date Dimension

  • Launch Excel
  • Go to Power Query tab. If you haven’t downloaded Power Query already you can do so here.
  • Select From Other Sources > Blank Query. This will launch the Power Query Editor .
  • Select Advanced Editor in either the Home or View tab of the editor.
  • Remove any code that the editor is currently story and replace it with the following:
//Create Date Dimension
(StartDate as date, EndDate as date)=>
    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
    //Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate),
    //Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount, 
    //Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    //Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),
    //Add Quarter Column
    QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
    //Add Week Number Column
    WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
    each Date.WeekOfYear([Date])),
    //Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),
    //Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText([Date],"MMMM")),
    //Add Day of Week Column
    DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
    each Date.ToText([Date],"dddd"))

You’ll find documentation for this script embedded in the code to help guide you through what each expression is doing.

  • Click OK. This query is actually a function that accepts parameters so you will see that it’s waiting for you to invoke it with values.


  • Click Invoke and provide the range of dates that you would like the date table to return back. Then click OK

The results can now be integrated into your solution.  For example, you may add this to an existing Power Pivot data model by selecting Close & Load To.

I’ve seen many date tables with dozens of columns and a variety of types of dates.  I left this one fairly simple so you can add your own customizations to it.  Any thoughts on date fields that may be universally needed by others?  Let me know and I’ll work on adding it to the script.

Here’s a few other takes on solving this problem with Power Query:





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads