Creating Time (Hour) column/Table

Hi Anonymous ,

Paste this into a blank query in Power Query to create a dimension table similar to your Date table, but for every second of the day.

let
  hoursTable = Table.FromList({0..23}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  renHours = Table.RenameColumns(hoursTable, {{"Column1", "hours"}}),
  minutesTable = Table.FromList({0..59}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  secondsTable = Table.FromList({0..59}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  addCJminutes = Table.AddColumn(renHours, "minutes", each minutesTable),
  expandCJminutes = Table.ExpandTableColumn(addCJminutes, "minutes", {"Column1"}, {"minutes"}),
  addCJseconds = Table.AddColumn(expandCJminutes, "seconds", each secondsTable),
  expandCJseconds = Table.ExpandTableColumn(addCJseconds, "seconds", {"Column1"}, {"seconds"}),
  addTime = Table.AddColumn(expandCJseconds, "time", each Text.Combine({Text.From([hours], "en-GB"), Text.From([minutes], "en-GB"), Text.From([seconds], "en-GB")}, ":"), type text),
  chgAllTypes = Table.TransformColumnTypes(addTime, {{"time", type time}, {"hours", Int64.Type}, {"minutes", Int64.Type}, {"seconds", Int64.Type}})
in
  chgAllTypes

It has a specific hours column if that's all you're interested in, but should be easy enough to edit it however you want.

Pete 

Hi Anonymous ,

You can also create a time table using DAX like below and set the data type as "Time":

Time Table =
VAR Hours_ =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 23, 1 ), "Hour_", [Value] )
VAR Minutes_ =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "Minute_", [Value] )
VAR Seconds_ =
    SELECTCOLUMNS ( GENERATESERIES ( 0, 59, 1 ), "Second_", [Value] )
RETURN
    SELECTCOLUMNS (
        CROSSJOIN ( Hours_, Minutes_, Seconds_ ),
        "Time",
            CONVERT ( [Hour_] & ":" & [Minute_] & ":" & [Second_], DATETIME )
    )

If you only need hours, try this:

Time Table just with Hours = 
SELECTCOLUMNS (
    GENERATESERIES ( 0, 23, 1 ),
    "Time", CONVERT ( [Value] & ":00:00", DATETIME )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.