Creating a Date Dimension with a Power Query Script

Chris Webb has previously blogged a similar approach, but I thought I’d shared my own version of a Power Query function that generates a table of dates that could be used to create a Date Dimension. I first saw Faisal Mohamood do this in our end to end Power BI talk at the PASS Summit, and have been using it as a quick way to show off the versatility of the Power Query formula language.

The function is included below:

How do I use this?

From the Power Query ribbon, select From Other Data Sources -> Blank Query.

In the query editor, click on the View tab, then click the Advanced Editor button on the ribbon.

Replace the default script with the CreateDateTable script, and click Done.

The Query Editor will now show the function definition, with an Invoke button.

Clicking the button will bring up a dialog that lets you enter values for each of the parameters. These are:

  • StartDate – the first date in your table
  • EndDate – the last date in your table
  • Culture – the culture of the table (optional). This gets passed to the Date.ToText function to display the month / weekday names. See the National Language Support (NLS) API for details.

After you Apply & Close, you’ll have a neatly formatted Date Dimension that can be imported directly into Power Pivot.

9 thoughts on “Creating a Date Dimension with a Power Query Script”

  1. Hi Matt,

    Great post, but a couple of minor issues that I think are worth noting:

    1) To get your DayCount vairable, you subtract the start date from the end date to get the number of days. In order to get the correct number of days though, you need to add one back. (Jan 31 – Jan 1 will give you 30 days, meaning the calendar output will actually end one day before the end date you provide.)

    2) InsertDayWeek generates a number between 0 and 6, with 0 representing Sunday, and 6 representing Saturday. This is due to the fact that in the M query language Date.DayOfWeek function returns values from a 0 based array, but it's inconsistent with the way that Excel pro's think of weekdays. Our Excel formulas that return the day of the week are all 1 based by default, starting with Sunday, so to get an output consistent with what we'd expect you also need to add 1 to the InsertDayWeek calculation as well.

    Given those though, this is a fantastic example of how to work with M. Really appreciate your sharing!

  2. Matt, Great stuff!

    I was wondering if you could tell me why just copying/pasting (duplicating) the lines that create the MonthName and DayOfWeekName columns, and changing the formatting codes from "MMMM" and "dddd" to "MMM" and "ddd" doesn't add those new columns?

    I'm new at this PowerQuery M-language stuff. I ended up just changing the format codes in the existing script to the shorter version; and that worked to get me the intended (Jan-Dec, and Sun-Mon) values I typically use.

    Chris Gilbert

  3. Hello,

    Would be nice to implement the fix in the provided source code…

    DayCount = Duration.Days(Duration.From(EndDate – StartDate))+1,

    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each (Date.DayOfWeek([Date])+1)),

    Thanks for the great example,

Leave a Reply

Your email address will not be published. Required fields are marked *