cancel
Showing results for 
Search instead for 
Did you mean: 

Time Dimension in Power Query

TLDR section

https://github.com/nolockcz/PowerQuery/tree/master/Time%20Dimension However, I personally recommend reading the article once before you use it in your project.

 

The long version

Do you remember my previous article about Solving Real Life Problems with Recursive Functions in Power Query? It is about splitting all datetime columns into 2 columns: date and time. It comes in hand if you want to achieve a high compression rate in your dataset, because the less unique values you have in a column the better it can be compressed. In the further article (Date Dimension with Holidays in Power Query) I explained how to create a date dimension in Power Query. What we are still missing is a time dimension. I have developed 2 different time dimensions in Power Query. One is in the 24-hour and the other in the 12-hour format.

A time dimension is very simple in comparison to a date dimension. A day has 24 hours, an hour 60 minutes, and a minute - 60 seconds. I don’t care about the leap second.

 

What are seconds good for?

Hmm, seconds. Do we really need them in a time dimension? I don’t think so. A time dimension having the smallest unit one minute contains 24 x 60 = 1440 rows. However, a time dimension which contains also seconds has 86 400 rows. Not only that. We also need this precision in a time column of a fact table. It means that a time column will also contain up to 86 400 unique values and won’t be able to be compressed so well. In my opinion, a granularity of one minute is good enough.

Let’s start with the code. At the beginning we create a table containing every minute of a day. For every minute we extract its hour and minute. Till now nothing spectacular.

 

/***** Table with one minute column *****/
    NumberOfMinutes = 24 * 60,
    ListOfMinutes = List.Times(#time(0, 0, 0), NumberOfMinutes, #duration(0, 0, 1, 0)),
    MinutesAsTable = Table.FromList(ListOfMinutes, Splitter.SplitByNothing(), {"Time"}, null, ExtraValues.Error),
    MinuteColumnAsTime = Table.TransformColumnTypes(MinutesAsTable,{{"Time", type time}}),

    /***** Index column *****/
    Index = Table.AddColumn(MinuteColumnAsTime, "Index", each Time.Hour([Time]) * 100 + Time.Minute([Time]), Int32.Type),

    /***** Hour and minute *****/
    Hour = Table.AddColumn(Index, "Hour", each Time.Hour([Time]), Int32.Type),
    Minute = Table.AddColumn(Hour, "Minute", each Time.Minute([Time]), Int32.Type),

 

The code generates a table with four columns:

0.PNG

 

Next step will be columns which are grouped by intervals like 5, 10, 15, 30, and 60 minutes. To achieve that we use a basic custom function, which rounds down minutes to the last fold of an interval.

 

/***** Round down minutes by interval value *****/
    fnTimeRoundDown = (currentTime as time, interval as number) as time => 
        let
            currentHour = Time.Hour(currentTime),
            currentMinute = Time.Minute(currentTime),
            result = #time(currentHour, currentMinute - Number.Mod(currentMinute, interval), 0)
        in
            result,
    FiveMinutes = Table.AddColumn(Minute, "FiveMinutes", each fnTimeRoundDown([Time], 5), type time),
    TenMinutes = Table.AddColumn(FiveMinutes, "TenMinutes", each fnTimeRoundDown([Time], 10), type time),
    FifteenMinutes = Table.AddColumn(TenMinutes, "FifteenMinutes", each fnTimeRoundDown([Time], 15), type time),
    ThirtyMinutes = Table.AddColumn(FifteenMinutes, "ThirtyMinutes", each fnTimeRoundDown([Time], 30), type time),
    OneHour = Table.AddColumn(ThirtyMinutes, "OneHour", each fnTimeRoundDown([Time], 60), type time),

 

A subset looks like:

0x.PNG

 

They are useful, but what our customers expect in their colorful visuals are intervals like from 10:00 to 10:15, from 10:15 to 10:30 and so on. For that we need another custom function.

 

/***** Generates a text from a time interval *****/
    fnTimeToText = (currentTime as time, countOfMinutes as number) as text =>
        let 
            from = currentTime,
            to = currentTime + #duration(0, 0, countOfMinutes, 0),
            result = Time.ToText(from) & " - " & Time.ToText(to)
        in
            result,
    FiveMinutesText = Table.AddColumn(OneHour, "FiveMinutesInterval", each fnTimeToText([FiveMinutes], 5), type text),
    TenMinutesText = Table.AddColumn(FiveMinutesText, "TenMinutesInterval", each fnTimeToText([TenMinutes], 10), type text),
    FifteenMinutesText = Table.AddColumn(TenMinutesText, "FifteenMinutesInterval", each fnTimeToText([FifteenMinutes], 15), type text),
    ThirtyMinutesText = Table.AddColumn(FifteenMinutesText, "ThirtyMinutesInterval", each fnTimeToText([ThirtyMinutes], 30), type text),
    OneHourText = Table.AddColumn(ThirtyMinutesText, "OneHourInterval", each fnTimeToText([OneHour], 60), type text),

 

Again a subset of generated data:

1.PNG

 

And in the end of the code, as usual, there is a translation section. You can change the column names to your language with ease.

 

The whole code:

let
       /**********************************************************
        * 
        * Author: Michal Dvorak WITH(NOLOCK)
        * Since: 02.08.2019
        * Twitter: @nolockcz
        *
        * Source code: https://github.com/nolockcz/PowerQuery
        *
        *********************************************************/    

    /***** Table with one minute column *****/
    NumberOfMinutes = 24 * 60,
    ListOfMinutes = List.Times(#time(0, 0, 0), NumberOfMinutes, #duration(0, 0, 1, 0)),
    MinutesAsTable = Table.FromList(ListOfMinutes, Splitter.SplitByNothing(), {"Time"}, null, ExtraValues.Error),
    MinuteColumnAsTime = Table.TransformColumnTypes(MinutesAsTable,{{"Time", type time}}),

    /***** Index column *****/
    Index = Table.AddColumn(MinuteColumnAsTime, "Index", each Time.Hour([Time]) * 100 + Time.Minute([Time]), Int32.Type),

    /***** Hour and minute *****/
    Hour = Table.AddColumn(Index, "Hour", each Time.Hour([Time]), Int32.Type),
    Minute = Table.AddColumn(Hour, "Minute", each Time.Minute([Time]), Int32.Type),

    /***** Round down minutes by interval value *****/
    fnTimeRoundDown = (currentTime as time, interval as number) as time => 
        let
            currentHour = Time.Hour(currentTime),
            currentMinute = Time.Minute(currentTime),
            result = #time(currentHour, currentMinute - Number.Mod(currentMinute, interval), 0)
        in
            result,

    FiveMinutes = Table.AddColumn(Minute, "FiveMinutes", each fnTimeRoundDown([Time], 5), type time),
    TenMinutes = Table.AddColumn(FiveMinutes, "TenMinutes", each fnTimeRoundDown([Time], 10), type time),
    FifteenMinutes = Table.AddColumn(TenMinutes, "FifteenMinutes", each fnTimeRoundDown([Time], 15), type time),
    ThirtyMinutes = Table.AddColumn(FifteenMinutes, "ThirtyMinutes", each fnTimeRoundDown([Time], 30), type time),
    OneHour = Table.AddColumn(ThirtyMinutes, "OneHour", each fnTimeRoundDown([Time], 60), type time),

    /***** Generates a text from a time interval *****/
    fnTimeToText = (currentTime as time, countOfMinutes as number) as text =>
        let 
            from = currentTime,
            to = currentTime + #duration(0, 0, countOfMinutes, 0),
            result = Time.ToText(from) & " - " & Time.ToText(to)
        in
            result,

    FiveMinutesText = Table.AddColumn(OneHour, "FiveMinutesInterval", each fnTimeToText([FiveMinutes], 5), type text),
    TenMinutesText = Table.AddColumn(FiveMinutesText, "TenMinutesInterval", each fnTimeToText([TenMinutes], 10), type text),
    FifteenMinutesText = Table.AddColumn(TenMinutesText, "FifteenMinutesInterval", each fnTimeToText([FifteenMinutes], 15), type text),
    ThirtyMinutesText = Table.AddColumn(FifteenMinutesText, "ThirtyMinutesInterval", each fnTimeToText([ThirtyMinutes], 30), type text),
    OneHourText = Table.AddColumn(ThirtyMinutesText, "OneHourInterval", each fnTimeToText([OneHour], 60), type text),
    
    /***** Translation of columns to German *****/
    ColumnTranslation = Table.RenameColumns(
        OneHourText,
        {
            {"Time", "Zeit"}, 
            {"Hour", "Stunde"}, 
            {"FiveMinutes", "5 Minuten"}, 
            {"TenMinutes", "10 Minuten"}, 
            {"FifteenMinutes", "15 Minuten"}, 
            {"ThirtyMinutes", "30 Minuten"}, 
            {"OneHour", "1 Stunde"}, 
            {"FiveMinutesInterval", "5 Minuten Intervall"}, 
            {"TenMinutesInterval", "10 Minuten Intervall"}, 
            {"FifteenMinutesInterval", "15 Minuten Intervall"}, 
            {"ThirtyMinutesInterval", "30 Minuten Intervall"}, 
            {"OneHourInterval", "1 Stunde Intervall"}
        }
    )
in
    ColumnTranslation

 

There is a big part of the world which doesn’t use 24-hour time format but instead 12-hours with meridiem (the a.m. and p.m. thing has a name). I have prepared a special edition of a time dimension.

2.jpg

(Source: https://travel.stackexchange.com/questions/34950/which-large-countries-use-12-hour-time-format-am-pm)

 

First of all, you can choose what format the meridiem should have. According to the internet you can write it in many ways. Do you prefer “a.m.” over “am”? No problem, just change it at the beginning of the Power Query query in the variables AmFormat and PmFormat.

The rest of the code is almost the same as the 24-hour format. I am not sure how am/pm is used in intervals if the start is before midday, but the end is after midday. And the same is around midnight. I have tried to find out the correct form on the internet but without success. I’ve decided to write both meridians if they are different like 11:45 am – 12:00 pm in comparison to 12:00 – 12:15 pm. If you expect another format, modify the code 😉

 

/***** Generates a text from a time interval *****/
    fnTimeToText = (currentTime as time, countOfMinutes as number) as text =>
        let 
            from = currentTime,
            to = currentTime + #duration(0, 0, countOfMinutes, 0),
            fromMeridiem = if Time.Hour(from) < 12 then amFormat else PmFormat,
            toMeridiem = if Time.Hour(to) < 12 then AmFormat else PmFormat,
            result = 
		Time.ToText(from, "h:mm") & 
                (if fromMeridiem = toMeridiem then "" else " " & fromMeridiem) & 
                " - " & 
                Time.ToText(to, "h:mm") & " " & toMeridiem
        in
            result,

 

Some examples when am switches to pm:

3.PNG

 

And the full code of the 12-hour time dimension.

let
       /**********************************************************
        * 
        * Author: Michal Dvorak WITH(NOLOCK)
        * Since: 02.08.2019
        * Twitter: @nolockcz
        *
        * Source code: https://github.com/nolockcz/PowerQuery
        *
        *********************************************************/ 
    
    /***** Format of the meridiem used in the code *****/
    AmFormat = "am",
    PmFormat = "pm",

    /***** Table with one minute column *****/
    NumberOfMinutes = 24 * 60,
    ListOfMinutes = List.Times(#time(0, 0, 0), NumberOfMinutes, #duration(0, 0, 1, 0)),
    MinutesAsTable = Table.FromList(ListOfMinutes, Splitter.SplitByNothing(), {"Time"}, null, ExtraValues.Error),
    MinuteColumnAsTime = Table.TransformColumnTypes(MinutesAsTable,{{"Time", type time}}),

    /***** Index column *****/
    Index = Table.AddColumn(MinuteColumnAsTime, "Index", each Time.Hour([Time]) * 100 + Time.Minute([Time]), Int32.Type),

    Meridiem = Table.AddColumn(Index, "Meridiem", each if Time.Hour([Time]) < 12 then AmFormat else PmFormat, type text),

    /***** Hour and minute *****/
    Hour24 = Table.AddColumn(Meridiem, "Hour24", each Time.Hour([Time]), Int32.Type),
    Hour12 = Table.AddColumn(Hour24, "Hour12", each if [Hour24] = 0 then 12 else if [Hour24] <= 12 then [Hour24] else [Hour24] - 12, Int32.Type),
    Minute = Table.AddColumn(Hour12, "Minute", each Time.Minute([Time]), Int32.Type),

    /***** Round down minutes by interval value *****/
    fnTimeRoundDown = (currentTime as time, interval as number) as time => 
        let
            currentHour = Time.Hour(currentTime),
            currentMinute = Time.Minute(currentTime),
            result = #time(currentHour, currentMinute - Number.Mod(currentMinute, interval), 0)
        in
            result,

    FiveMinutes = Table.AddColumn(Minute, "FiveMinutes", each fnTimeRoundDown([Time], 5), type time),
    TenMinutes = Table.AddColumn(FiveMinutes, "TenMinutes", each fnTimeRoundDown([Time], 10), type time),
    FifteenMinutes = Table.AddColumn(TenMinutes, "FifteenMinutes", each fnTimeRoundDown([Time], 15), type time),
    ThirtyMinutes = Table.AddColumn(FifteenMinutes, "ThirtyMinutes", each fnTimeRoundDown([Time], 30), type time),
    OneHour = Table.AddColumn(ThirtyMinutes, "OneHour", each fnTimeRoundDown([Time], 60), type time),

    /***** Generates a text from a time interval *****/
    fnTimeToText = (currentTime as time, countOfMinutes as number) as text =>
        let 
            from = currentTime,
            to = currentTime + #duration(0, 0, countOfMinutes, 0),
            fromMeridiem = if Time.Hour(from) < 12 then amFormat else PmFormat,
            toMeridiem = if Time.Hour(to) < 12 then AmFormat else PmFormat,
            result = 
		Time.ToText(from, "h:mm") & 
                (if fromMeridiem = toMeridiem then "" else " " & fromMeridiem) & 
                " - " & 
                Time.ToText(to, "h:mm") & " " & toMeridiem
        in
            result,

    FiveMinutesText = Table.AddColumn(OneHour, "FiveMinutesInterval", each fnTimeToText([FiveMinutes], 5), type text),
    TenMinutesText = Table.AddColumn(FiveMinutesText, "TenMinutesInterval", each fnTimeToText([TenMinutes], 10), type text),
    FifteenMinutesText = Table.AddColumn(TenMinutesText, "FifteenMinutesInterval", each fnTimeToText([FifteenMinutes], 15), type text),
    ThirtyMinutesText = Table.AddColumn(FifteenMinutesText, "ThirtyMinutesInterval", each fnTimeToText([ThirtyMinutes], 30), type text),
    OneHourText = Table.AddColumn(ThirtyMinutesText, "OneHourInterval", each fnTimeToText([OneHour], 60), type text),

    /***** Translation of columns to English *****/
    ColumnTranslation = Table.RenameColumns(
        OneHourText,
        {
            {"Time", "Time"}, 
            {"Meridiem", "Meridiem"},
            {"Hour24", "Hour 24h"}, 
            {"Hour12", "Hour 12h"}, 
            {"Minute", "Minute"}, 
            {"FiveMinutes", "5 Minutes"}, 
            {"TenMinutes", "10 Minutes"}, 
            {"FifteenMinutes", "15 Minutes"}, 
            {"ThirtyMinutes", "30 Minutes"}, 
            {"OneHour", "1 Hour"}, 
            {"FiveMinutesInterval", "5 Minutes Interval"}, 
            {"TenMinutesInterval", "10 Minutes Interval"}, 
            {"FifteenMinutesInterval", "15 Minutes Interval"}, 
            {"ThirtyMinutesInterval", "30 Minutes Interval"}, 
            {"OneHourInterval", "1 Hour Interval"}
        }
    )
in
    ColumnTranslation

 

From community to community

Both versions, 24-hour and 12-hour, can be found on the GitHub: https://github.com/nolockcz/PowerQuery/tree/master/Time%20Dimension.

Please check out the code from GitHub, hack, and report bugs and ideas for improvements. Every constructive feedback is very welcomed. I hope that the community together can create a new etalon of a time dimension in Power Query!