Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Generating A Date Dimension Table In Power Query

with 28 comments

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:

let

    CreateDateTable = (StartDate, EndDate) =>

let

    /*StartDate=#date(2012,1,1),

    EndDate=#date(2013,12,31),*/

    //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),

    //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]))

in

    IsToday

in

    CreateDateTable

 

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:

image

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

let

    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})

in

    CallCreateDateTable

Here’s the output:

image

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.

Written by Chris Webb

November 19, 2013 at 5:32 pm

Posted in Power BI, Power Query

28 Responses

Subscribe to comments with RSS.

  1. Did you intend to leave a link in your last bullet?
    https://cwebbbi.wordpress.com/2013/01/24/building-relative-date-reports-in-powerpivot/

    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.

    ruve1k

    November 19, 2013 at 8:56 pm

    • Yes, I did forget that link, thanks!

      Sorry, I don’t have a list of file properties… I only have the Language and Library Specification documents.

      Chris Webb

      November 19, 2013 at 9:00 pm

  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 http://www.timeanddate.com/holidays/uk/ 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) =>
    let
    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”}})

    in
    RenamedColumns1
    in
    paramquery

    Steven Peters

    November 20, 2013 at 10:30 am

  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 “)),

    Steven Peters

    November 20, 2013 at 10:39 am

    • Thanks. The basic graphical interface brings up the Server and Table as
      let
      Source = Sql.Databases(“servername”),
      speedus = Source{[Name="databasename"]}[Data],
      Tablename = datbasename{[Name="Tablename"]}[Data]
      in
      Tablename

      Or if I select an fxfunction in the database

      GetComponentFromKey = databsename{[Name="GetComponentFromKey"]}[Data]
      in
      GetComponentFromKey

      Although I get a message “function (@key as nullable text as nullable number” with an Invoke button that I can press an enter an initial parameter.

      But it doesn’t see the stored procedures in the database for me to select.
      I guess I could just manually enter the Proc name in place of the function and see what goes.

      nt4boy

      November 28, 2013 at 10:02 am

  4. 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

    Steven Peters

    December 5, 2013 at 4:15 pm

    • 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.

      nt4boy

      December 5, 2013 at 4:27 pm

  5. 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!
    Simona

    Simona

    January 14, 2014 at 3:14 pm

    • 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.

      Chris Webb

      January 14, 2014 at 3:17 pm

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

        Simona

        January 14, 2014 at 3:38 pm

  6. 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?

    let
    // Choose Start & End Date
    StartDate=#date(2013,1,1),
    EndDate=#date(2015,12,31),

    // 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")}," "))

    in
    WeekNameOfYear

    Bellicose

    February 8, 2014 at 1:44 am

    • Can you give me a few more details about what’s going wrong and what you’d like to happen please?

      Chris Webb

      February 9, 2014 at 8:26 pm

      • 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.

        Bellicose

        February 10, 2014 at 4:37 pm

      • 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)?

        Chris Webb

        February 10, 2014 at 4:44 pm

    • 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))),

      Bellicose

      February 10, 2014 at 5:29 pm

  7. […] 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. […]

  8. […] very good idea to have a separate Date table in the Excel Data Model to use with it. I’ve already blogged about how to use a function to generate a Date table in Power Query (as has Matt Masson, whose […]

  9. […] very good idea to have a separate Date table in the Excel Data Model to use with it. I’ve already blogged about how to use a function to generate a Date table in Power Query (as has Matt Masson, whose […]

  10. […] The following technique is a modification to Chris Webb’s excellent Power Query article located at: https://cwebbbi.wordpress.com/2013/11/19/generating-a-date-dimension-table-in-power-query/ […]

  11. Just posted a technique that produces a dynamic effect for the PQ model presented here. Thanks for all of the great inspiration you give.

    http://dhexcel1.wordpress.com/2014/04/03/dynamic-write-back-to-sync-powerpivot-tables-with-date-tables-created-by-power-query/

    David Hager

    April 4, 2014 at 12:22 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,859 other followers

%d bloggers like this: