August 2017 – Michael Adrian Johnson

A common requirement in many BI environments including PowerBI is a date table. Date tables are a great way to add extra depth to you period\time sensitive reporting and are essential when using the time intelligence features of DAX.

There are a couple of options you have when creating a date-table such as importing the table from another data source such as a data warehouse or you can create the date-table using the CALENDAR function in the data model. In this post, we will look at how a date-table can be created using PowerQuery (also known as M).

At the heart of any date table, we require a column of distinct, continuous dates for a given date range. Fortunately, PowerQuery has a function List.Dates that can create such a table with a few quirks\features. Firstly this function returns a list and not a table, to solve this we can use the Table.FromList function to convert the list to a table. Secondly unlike the CALENDAR function in DAX which takes a start and end date as parameters the List.Dates function instead takes a start date, count of periods that need to be created and finally a duration. this duration can be in days, hours, minutes or seconds, in our case, we would want to use days. Unless you know the exact number of days that you need in your date table it may be easier to calculate the number of days using the Duration.Days function which will return the number of days between two dates. The code sample below can be used to create a list of dates between the StartDate and EndDate.

    StartDate = #date(2017,1,1),
    EndDate = #date(2017,12,31),
    NumberOfDays = Duration.Days(Duration.From(EndDate-StartDate)),
    Source = List.Dates(StartDate,NumberOfDays ,#duration(1,0,0,0))

The next step is to convert this list into a table using the Table.FromList function, which also allows us to provide a better column name for our date column, this column also need to be set to a date data type.

    TableFromList = Table.FromList(Source,Splitter.SplitByNothing(),{"Date"}),
    ChangeType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}})

This simple table with the correct relationships in your data model will allow you to use the powerful time intelligence functions that DAX supports, however, we want to create our own custom date attributes that allow for more natural slicing, we will do this by adding additional columns using the Table.AddColumn function along with the Date family of functions.

    Year = Table.AddColumn(ChangeType ,"Year",each Date.Year([Date]),type number),
    MonthOfYearNumber = Table.AddColumn(Year ,"Month of Year Number",each Date.Month([Date]),type number),
    MonthOfYearName = Table.AddColumn(MonthOfYearNumber ,"Month of year",each Date.ToText([Date],"MMMM"),type text),
    DayOfMonth = Table.AddColumn(MonthOfYearName ,"Day of Month",each Date.Day([Date]),type number),
    DayOfWeekNumber = Table.AddColumn(DayOfMonth,"Day of Week number",each Date.DayOfWeek([Date]),type text),
    DayOfWeekName = Table.AddColumn(DayOfWeekNumber ,"Day of week",each Date.ToText([Date],"dddd")),
    PeriodNumber = Table.AddColumn(DayOfWeekName,"Period Number",each Date.ToText([Date],"yyyyMM"), type number),
    Period = Table.AddColumn(PeriodNumber ,"Period",each Date.ToText([Date],"MMM") &amp; "-" &amp; Date.ToText([Date],"yy"))

Feel free to add and remove any columns that you would find useful.

The last thing that we may consider doing to make this code more useful is to convert the code block into a function, this will allow us to create multiple date-tables if required but more importantly, it will make using a dynamic start and end date much easier. The final code block would look like this.


let CreateDateTable = (StartDate as date,EndDate as date) as table =&gt;
    let
        NumberOfDays = Duration.Days(Duration.From(EndDate-StartDate)),
        Source = List.Dates(StartDate,NumberOfDays ,#duration(1,0,0,0)),
        TableFromList = Table.FromList(Source,Splitter.SplitByNothing(),{"Date"}),
        ChangeType = Table.TransformColumnTypes(TableFromList,{{"Date", type date}}),
        Year = Table.AddColumn(ChangeType ,"Year",each Date.Year([Date]),type number),
        MonthOfYearNumber = Table.AddColumn(Year ,"Month of Year Number",each Date.Month([Date]),type number),
        MonthOfYearName = Table.AddColumn(MonthOfYearNumber ,"Month of year",each Date.ToText([Date],"MMMM"),type text),
        DayOfMonth = Table.AddColumn(MonthOfYearName ,"Day of Month",each Date.Day([Date]),type number),
        DayOfWeekNumber = Table.AddColumn(DayOfMonth,"Day of Week number",each Date.DayOfWeek([Date]),type text),
        DayOfWeekName = Table.AddColumn(DayOfWeekNumber ,"Day of week",each Date.ToText([Date],"dddd")),
        PeriodNumber = Table.AddColumn(DayOfWeekName,"Period Number",each Date.ToText([Date],"yyyyMM"), type number),
        Period = Table.AddColumn(PeriodNumber ,"Period",each Date.ToText([Date],"MMM") &amp; "-" &amp; Date.ToText([Date],"yy"))
    in
        Period
in
    CreateDateTable

If you have never created a blank query before then you can use the following steps to implement this code.

From the Query Editor select Get data –> Other –> Blank Query

This creates a new query, select the advanced editor from the Home ribbon

Using the Advanced editor replace the default code with final code sample from above

Invoke the function by selecting it from the list of queries (I have renamed the Query to make it more usable), capture the start and end dates using the calendar controls then click on the [invoke] button.