Creating a Time Dimension using the Power Query Editor in Power BI Desktop
Below is the syntax to create a Time Dimension Table in Power Query
I had a requirement where I needed to create a Time Dimension for a customer. Most of the time I only need the date. Upon searching I could not find a resource where they had created the time dimension only using Power Query.
Reference: https://docs.microsoft.com/en-us/powerquery-m/list-times
- What I did was I opened my Power BI Desktop file and went into the Query Editor
-
I then clicked on Get Data and selected Blank Query
- I renamed the Query to Time
-
I then clicked on the Advanced Editor to allow me to paste in my code
- I then pasted in the code below
let
Source = List.Times(#time(0, 0, 0), 1440, #duration(0, 0, 1, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Time", "Time - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Time - Copy", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Time - Copy.1", "Time - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time - Copy.1", Int64.Type}, {"Time - Copy.2", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Time - Copy.1", "Hour of Day"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns1", "Time - Copy.2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Time - Copy.2.1", "Time - Copy.2.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Time - Copy.2.1", Int64.Type}, {"Time - Copy.2.2", type text}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"Time - Copy.2.1", "Minute"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns2", "24 Hour", each if [#"Time - Copy.2.2"] = "AM" then [Hour of Day] else [Hour of Day] + 12),
#"Renamed Columns3" = Table.RenameColumns(#"Added Conditional Column",{{"Time - Copy.2.2", "AM/PM"}}),
#"5 Mins" = Table.AddColumn(#"Renamed Columns3", "5 Mins", each if Number.IntegerDivide([Minute],5) = 0 then "00:00"
else if Number.IntegerDivide([Minute],5) = 1 then "00:05"
else if Number.IntegerDivide([Minute],5) = 2 then "00:10"
else if Number.IntegerDivide([Minute],5) = 3 then "00:15"
else if Number.IntegerDivide([Minute],5) = 4 then "00:20"
else if Number.IntegerDivide([Minute],5) = 5 then "00:25"
else if Number.IntegerDivide([Minute],5) = 6 then "00:30"
else if Number.IntegerDivide([Minute],5) = 7 then "00:35"
else if Number.IntegerDivide([Minute],5) = 8 then "00:40"
else if Number.IntegerDivide([Minute],5) = 9 then "00:45"
else if Number.IntegerDivide([Minute],5) = 10 then "00:50"
else "00:55"),
#"15 Mins" = Table.AddColumn(#"5 Mins", "15 Mins", each if Number.IntegerDivide([Minute],15) = 0 then "00:00"
else if Number.IntegerDivide([Minute],15) = 1 then "00:15"
else if Number.IntegerDivide([Minute],15) = 2 then "00:30"
else "00:45"),
#"30 Mins" = Table.AddColumn(#"15 Mins", "30 Mins", each if Number.IntegerDivide([Minute],30) = 0 then "00:00"
else "00:30"),
#"Changed Type3" = Table.TransformColumnTypes(#"30 Mins",{{"Time", type time}})
in
#"Changed Type3"
- I then clicked Ok
- And below I could then see the output of my table.
- My final step was to click Close and Apply
- I then created a simple report where I could see some of the table properties
Conclusion
I have shown in my blog post how to create a time dimension using only the Power Query Editor.
If there are any comments or suggestions, please let me know in the section below.
If you would like a copy of the file you can get it here: Time Dimension.pbix
Thanks for reading.
[…] Gilbert Quevauvilliers has a script to generate a time dimension as well: […]
Cool, thanks!
How can I change the script to reflect 12 pm as 12 in 24 and 12am as 1.
Hi Cyndi, thanks for the question.
What you could do is to create a new conditional column and then add a condition for when it is 12 then 24 and when it is 12am then 1?
This can also be achieved by using the Time functions. Then you don’t have to treat it like text and you get rid of all the conditionals. I created a second-by-second time dimension by using List.Times, Time.Hour, Time.Minute and Integer division to get the 5,15 and 30 min intervals.
I thought you might think its interesting, so below is the code 🙂
In
let
Source = List.Times(#time(0, 0, 0), Duration.TotalSeconds(#duration(1, 0, 0, 0)), #duration(0, 0, 0, 1)),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), {“Second”}),
#”Added Hour” = Table.AddColumn(#”Converted to Table”, “Hour”, each Time.Hour([Second]), Int64.Type),
#”Added Minute” = Table.AddColumn(#”Added Hour”, “Minute”, each Time.Minute([Second]), Int64.Type),
#”Added 5 min” = Table.AddColumn(#”Added Minute”, “5 min”, each #time(0, Number.IntegerDivide(Time.Minute([Second]), 5) * 5, 0), Time.Type),
#”Added 15 min” = Table.AddColumn(#”Added 5 min”, “15 min”, each #time(0, Number.IntegerDivide(Time.Minute([Second]), 15) * 15, 0), Time.Type),
#”Added 30 min” = Table.AddColumn(#”Added 15 min”, “30 min”, each #time(0, Number.IntegerDivide(Time.Minute([Second]), 30) * 30, 0), Time.Type)
in
#”Added 30 min”
Hi Henrik,
That is awesome thanks for sharing and very useful!