Today's post is going to be short and sweet. I'm simply publishing my version of a DimTime table for Power BI.
Thanks to Radacad for posting a great article on why we need a DimTime table, along with their script for creating one in Power BI.
If you don't already have a DimDate table, you can find my version in my DimDate: What, Why and How blog article. I update the script occasionally with new requests.
Quickly Create a DimDate Table in Power Query (M code)
Follow the instructions below to create a quick, simple yet effective DimDate table in Power Query:
From the Power Query Editor, in the Home tab, click New Source > Blank Query.
Rename the Query1 to DimTime in the Query Settings pane on the right (if you don't see this pane tick the box in the View tab in the ribbon to turn it on).
Click Advanced Editor in the Home tab in the ribbon.
Replace all the code in the Advanced Editor window with the below code:
let
Source = List.Times(#time(0,0,0),24*60*60,#duration(0,0,0,1)),
#"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 time}}),
#"Added TimeKey" = Table.AddColumn(#"Changed Type", "TimeKey", each Time.ToText( [Time], "HHmmss"), type text),
#"Inserted Hour" = Table.AddColumn(#"Added TimeKey", "Hour", each Time.Hour([Time]), Int64.Type),
#"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time]), Int64.Type),
#"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time]), type number),
#"Added AM PM" = Table.AddColumn(#"Inserted Second", "AM/PM", each if [Hour] < 12 then "a.m." else "p.m."),
#"Added Index" = Table.AddIndexColumn(#"Added AM PM", "Index", 0, 1),
#"Inserted Hour (12hr)" = Table.AddColumn(#"Added Index", "Hour (12hr)", each Replacer.ReplaceValue( Number.Mod([Hour], 12), 0, 12), type number),
#"Inserted 8 Hour Sort" = Table.AddColumn(#"Inserted Hour (12hr)", "8 Hour Sort", each Number.IntegerDivide([Hour], 8), Int64.Type),
#"Added 8 Hour Groups" = Table.AddColumn(#"Inserted 8 Hour Sort", "8 Hour Groups", each if [8 Hour Sort] = 0 then "Midnight to 8am" else if [8 Hour Sort] = 1 then "8am to 4pm" else if [8 Hour Sort] = 2 then "4pm to Midnight" else "Not Specified"),
#"Inserted 6 Hour Sort" = Table.AddColumn(#"Added 8 Hour Groups", "6 Hour Sort", each Number.IntegerDivide([Hour], 6), Int64.Type),
#"Added 6 Hour Groups" = Table.AddColumn(#"Inserted 6 Hour Sort", "6 Hour Groups", each if [6 Hour Sort] = 0 then "Midnight to 6am" else if [6 Hour Sort] = 1 then "6am to noon" else if [6 Hour Sort] = 2 then "noon to 6pm" else if [6 Hour Sort] = 3 then "6pm to midnight" else "Not Specified"),
#"Inserted 4 Hour Sort" = Table.AddColumn(#"Added 6 Hour Groups", "4 Hour Sort", each Number.IntegerDivide([Hour], 4), Int64.Type),
#"Added 4 Hour Groups" = Table.AddColumn(#"Inserted 4 Hour Sort", "4 Hour Groups", each if [4 Hour Sort] = 0 then "Midnight to 4am" else if [4 Hour Sort] = 1 then "4am to 8am" else if [4 Hour Sort] = 2 then "8am to noon" else if [4 Hour Sort] = 3 then "noon to 4pm" else if [4 Hour Sort] = 4 then "4pm to 8pm" else if [4 Hour Sort] = 5 then "8pm to midnight" else "Not Specified"),
#"Inserted 3 Hour Sort" = Table.AddColumn(#"Added 4 Hour Groups", "3 Hour Sort", each Number.IntegerDivide([Hour], 3), Int64.Type),
#"Added 3 Hour Groups" = Table.AddColumn(#"Inserted 3 Hour Sort", "3 Hour Groups", each if [3 Hour Sort] = 0 then "Midnight to 3am" else if [3 Hour Sort] = 1 then "3am to 6am" else if [3 Hour Sort] = 2 then "6am to 9am" else if [3 Hour Sort] = 3 then "9am to noon" else if [3 Hour Sort] = 4 then "noon to 3pm" else if [3 Hour Sort] = 5 then "3pm to 6pm" else if [3 Hour Sort] = 6 then "6pm to 9pm" else if [3 Hour Sort] = 7 then "9pm to Midnight" else "Not Specified"),
#"Inserted 30 Minute Intervals" = Table.AddColumn(#"Added 3 Hour Groups", "30 Minute Intervals", each Number.IntegerDivide([Minute], 30)*30, Int64.Type),
#"Inserted 15 Minute Intervals" = Table.AddColumn(#"Inserted 30 Minute Intervals", "15 Minute Intervals", each Number.IntegerDivide([Minute], 15)*15, Int64.Type),
#"Inserted 10 Minute Intervals" = Table.AddColumn(#"Inserted 15 Minute Intervals", "10 Minute Intervals", each Number.IntegerDivide([Minute], 10)*10, Int64.Type),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted 10 Minute Intervals",{{"AM/PM", type text}, {"4 Hour Groups", type text}, {"3 Hour Groups", type text}, {"6 Hour Groups", type text}, {"8 Hour Groups", type text}})
in
#"Changed Type1"
Click Done to close the Advanced Editor.
Use the options in the Add Column tab in the ribbon to add any additional Time information you might need.
Close and Apply.
