Creating Time (Hour) column/Table

[Anonymous]

Hi All, 

Can you please help me with the below issue? 

• I have a report that includes 8 tables (one of which is the Date table) 
• The query is pulled from microsoft sql server
• I split the Datestamp columns to "Date" and "Time" and changed their data types to reflect correctly.
• Time type is HH:MM:SS - but i'm only intersted in Hours honestly.

One of the tables is called "All tickets" - which includes the Ticket ID, Created time and Completed time and others non-related columns.

As a start I want to create a a visual "Cout of Request ID VS Created time" and "Cout of Request ID VS Created time" (So like when do I receive most tickets and when do I close most) 

I searched many fixes on the internet but none worked with my model. 

Can you please help me create a Time Table such as the Date table where I can connect it to all my tables and use it accordingly? 

[BA_Pete · Accepted solution]

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 

[Icey · Accepted solution]

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.

[BA_Pete · Accepted solution]

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