Creating a custom date table using PowerQuery

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") & "-" & 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 =>
    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") & "-" & 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

BlankQuery.PNG

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

BlankQuery2.png

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

BlankQuery3.png

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.

BlankQuery4.png

You will now have a custom date table that you are able to use in your data model.

BlankQuery5.png

I’m speaking: PASS Business Analytics Marathon

PASS_17_BAMarathon_Banner_440x220.jpg

I will be presenting a webinar on 7 June for the PASS Business Analytics marathon sponsored by Quest.

During my talk, I will introduce attendees to streaming data and how it different from how we as data professionals have traditionally dealt with data. We will then look at how Azure Stream Analytics can be used to provide rich real-time reporting.

We will conclude the talk with a practical demonstration of creating a streaming analytics Job in Azure and use PowerBI to visualize the results.

Presentation code:
You can download the code I used to create the events here

Importing data from web pages using Power BI

Sometimes the data we need for our data models can only found on web pages. The challenge with web pages is that they are often poorly structured and contain a mix of content types such as tables, lists and images, this makes importing data challenging so is often omitted in most ETL tools. Fortunately importing data from websites is made pretty easy in Power BI. In this post, we will import a list of countries along with its populationRead More »

Importing data into Power BI desktop using R

Power Bi is a great tool for data discovery with new functionality being made available all the time. However, this functionality is still limited when compared to a mature platform like R and you may come across scenarios where R is a better choice. It is for just such cases that Power BI now supports R script as a data source. Three scenarios come to mind where this might want to do this:

Read More »

SatRday Cape Town – I’m speaking

satrdaylogo-squareSQL Saturdays are popular events with many of these events taking place every weekend all over the world. Well, the R community is getting in on the action and I am excited to have been selected to speak at only the second event taking place in Cape Town on the 18th February 2017.

I will be talking about how Power BI  can be extended using R scripts and visualisations. There is still of space available with some amazing speakers so make sure to register.

Update: The event is now fully booked. See you all there.