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:
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:
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:
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.
(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:
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!
- lakshmanakumar on: Comparing Selected Month Sales against other month...
- yodaOG on: [PowerBi][UI] Animated Cards with HTML y CSS
- nico12 on: Icons & Data Labels on Top of the Bars with Condit...
- Aditya07 on: New July 2023 update on Line chart visual
-
Pragati11
on:
Power BI – The New Card Visual Part 1
- powercdc on: Domain (Preview)-Power BI Services
- Aditya07 on: How to hide visual based upon slicer selection
- JamesPearson83 on: Analyzing Sales for a Specific Time Period in Powe...
-
nandukrishnavs
on:
How can I show the legend and data labels together...
- APQueiroz on: How to Transform a Line Chart into an Insightful K...