Time Table (Power Query M function)

Hi everyone,

@DavidNealon thought it would be a nice addition if there was a Time Table Power Query M code function available in this category as well. Now I can’t take any credit because it was designed by Gina Meronek, you can find it and the full article she wrote here.

When invoked the function creates a Time Table at a granularity of Minutes. If your requirement is different that can easily be fixed. Note that other changes might be required as well but to give you some ideas:
For Hourly intervals: Source = List.Times(#time(0, 0, 0), 24, #duration(0, 1, 0, 0))
30 minute intervals: Source = List.Times(#time(0, 0, 0), 48, #duration(0, 0, 30, 0))
15 minute intervals: Source = List.Times(#time(0, 0, 0), 96, #duration(0, 0, 15, 0))

You can add as many attributes to the table as required, examples present are Working hours and Period of Day but if Shifts or some other kind of grouping make sense to your business feel free to share that with the community in a reply :wink:.

let CreateTimeTable = () as table =>
  let
    Source = List.Times(#time(0, 0, 0), 1440, #duration(0,0,1,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), 
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type time}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Time"}}),
    InsertHour = Table.AddColumn(RenamedColumns, "Hour", each Time.StartOfHour([Time]), type time),
    InsertMinute = Table.AddColumn(InsertHour, "Minute Number", each Time.Minute([Time]), Int64.Type),
    InsertQuarterHour = Table.AddColumn(InsertMinute, "Quarter Hour", each if [Minute Number]<15 then [Hour] else if [Minute Number] < 30 then Value.Add([Hour],#duration(0,0,15, 0)) else if [Minute Number] < 45 then Value.Add([Hour],#duration(0,0,30, 0)) else Value.Add([Hour],#duration(0,0,45, 0)), type time),
    InsertHourNumber = Table.AddColumn(InsertQuarterHour, "Hour Number", each Time.Hour([Time]), Int64.Type),  
    InsertNextHour = Table.AddColumn(InsertHourNumber, "Next Hour", each Value.Add([Hour],#duration(0,1,0, 0)), type time),  
    InsertNextQuarterHour = Table.AddColumn(InsertNextHour, "Next Quarter Hour", each Value.Add([Quarter Hour],#duration(0,0,15, 0)), type time),  
    InsertWorkingHours = Table.AddColumn(InsertNextQuarterHour, "Working Hours",   each if [Hour Number] >= 8 and [Hour Number] < 17 then "Standard" else if [Hour Number] >= 17 and [Hour Number] < 21 then "Overtime" else null, type text),  
    InsertPeriod = Table.AddColumn(InsertWorkingHours, "Period of Day",   each if [Hour Number] >= 0 and [Hour Number] < 4 then "After Midnight" else   if [Hour Number] >= 4 and [Hour Number] < 8 then "Early Morning" else  if [Hour Number] >= 8 and [Hour Number] < 12 then "Late Morning" else  if [Hour Number] >= 12 and [Hour Number] < 16 then "Afternoon" else  if [Hour Number] >= 16 and [Hour Number] < 20 then "Evening" else "Late Night", type text),  
    InsertPeriodSort = Table.AddColumn(InsertPeriod, "PeriodOfDaySort", each   if [Hour Number] >= 0 and [Hour Number] < 4 then 0 else   if [Hour Number] >= 4 and [Hour Number] < 8 then 1 else  if [Hour Number] >= 8 and [Hour Number] < 12 then 2 else  if [Hour Number] >= 12 and [Hour Number] < 16 then 3 else  if [Hour Number] >= 16 and [Hour Number] < 20 then 4 else 5, Int64.Type),
    InsertTimeKey = Table.AddColumn(InsertPeriodSort, "TimeKey", each Time.ToText([Time], "HHmm"), type text)
  in
  InsertTimeKey
in
CreateTimeTable

.
Tip. If you want to add the Time Table without having to Invoke the function, you can omit the first let expression and the last in clause.

10 Likes

Marking the contribution as closed.

Hi All.

I needed a time table with a few bins for a recent project and thought I’d post a more fully-binned example in case it might be helpful for someone.

Greg

let
    #"=== START" = "START",
    MinuteCount = 24 * 60, // 1440
    Source = List.Times(#time(0, 0, 0), MinuteCount, #duration(0, 0, 1, 0)),
    ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenameColumns = Table.RenameColumns(ConvertToTable,{{"Column1", "Time"}}),
    ChangeType = Table.TransformColumnTypes(RenameColumns,{{"Time", type time}}),

    // INSERT CORE FIELDS
    #"=== INSERT CORE FIELDS" = ChangeType,
    InsertTimeKey = Table.AddColumn(#"=== INSERT CORE FIELDS", "TimeKey", each Time.Hour([Time])*100 + Time.Minute([Time]), Int32.Type),
    InsertTimeKeyText = Table.AddColumn(InsertTimeKey, "TimeKeyText", each Text.End("0000" & Text.From([TimeKey]), 4), type text),

    InsertHour24 = Table.AddColumn(InsertTimeKeyText, "Hour 24", each Time.Hour([Time]), Int32.Type),
    InsertHour12 = Table.AddColumn(InsertHour24, "Hour 12", each if Time.Hour([Time]) <= 12 then Time.Hour([Time]) else Time.Hour([Time]) - 12, Int32.Type),
    InsertMinute = Table.AddColumn(InsertHour12, "Minute", each Time.Minute([Time]), Int32.Type),

    InsertAMPM = Table.AddColumn(InsertMinute, "AMPM", each if Time.Hour([Time]) < 12 then "AM" else "PM", type text),

    InsertPeriod = Table.AddColumn(InsertAMPM, "Period of Day", each 
        if Time.Hour([Time]) < 4 then "After Midnight" else 
        if Time.Hour([Time]) < 8 then "Early Morning" else 
        if Time.Hour([Time]) < 12 then "Morning" else 
        if Time.Hour([Time]) < 16 then "Afternoon" else 
        if Time.Hour([Time]) < 20 then "Evening" else 
        "Night", type text),
    InsertPeriodSort = Table.AddColumn(InsertPeriod, "Period of Day Sort", each 
        if Time.Hour([Time]) < 4 then 1 else 
        if Time.Hour([Time]) < 8 then 2 else 
        if Time.Hour([Time]) < 12 then 3 else 
        if Time.Hour([Time]) < 16 then 4 else 
        if Time.Hour([Time]) < 20 then 5 else 
        6, Int32.Type),

    // INSERT HOUR BINS
    #"=== INSERT HOUR BINS" = InsertPeriodSort,
    InsertHourBin12 = Table.AddColumn(#"=== INSERT HOUR BINS", "Hour Bin 12", each 
        if Time.Hour([Time]) < 12 then "0-11" else 
        "12-23", type text),
    InsertHourBin12Sort = Table.AddColumn(InsertHourBin12, "Hour Bin 12 Sort", each Number.IntegerDivide(Time.Hour([Time]), 12) + 1, Int32.Type),
    
    InsertHourBin8 = Table.AddColumn(InsertHourBin12Sort, "Hour Bin 8", each 
        if Time.Hour([Time]) < 8 then "0-7" else 
        if Time.Hour([Time]) < 16 then "8-15" else 
        "16-23", type text),
    InsertHourBin8Sort = Table.AddColumn(InsertHourBin8, "Hour Bin 8 Sort", each Number.IntegerDivide(Time.Hour([Time]), 8) + 1, Int32.Type),

    InsertHourBin6 = Table.AddColumn(InsertHourBin8Sort, "Hour Bin 6", each 
        if Time.Hour([Time]) < 6 then "0-5" else 
        if Time.Hour([Time]) < 12 then "6-11" else 
        if Time.Hour([Time]) < 18 then "12-17" else 
        "18-23", type text),
    InsertHourBin6Sort = Table.AddColumn(InsertHourBin6, "Hour Bin 6 Sort", each Number.IntegerDivide(Time.Hour([Time]), 6) + 1, Int32.Type),

    InsertHourBin4 = Table.AddColumn(InsertHourBin6Sort, "Hour Bin 4", each 
        if Time.Hour([Time]) < 4 then "0-3" else 
        if Time.Hour([Time]) < 8 then "4-7" else 
        if Time.Hour([Time]) < 12 then "8-11" else 
        if Time.Hour([Time]) < 16 then "12-15" else 
        if Time.Hour([Time]) < 20 then "16-19" else 
        "20-23", type text),
    InsertHourBin4Sort = Table.AddColumn(InsertHourBin4, "Hour Bin 4 Sort", each Number.IntegerDivide(Time.Hour([Time]), 4) + 1, Int32.Type),

    InsertHourBin3 = Table.AddColumn(InsertHourBin4Sort, "Hour Bin 3", each 
        if Time.Hour([Time]) < 3 then "0-2" else 
        if Time.Hour([Time]) < 6 then "3-5" else 
        if Time.Hour([Time]) < 9 then "6-8" else 
        if Time.Hour([Time]) < 12 then "9-11" else 
        if Time.Hour([Time]) < 15 then "12-14" else 
        if Time.Hour([Time]) < 18 then "15-17" else 
        if Time.Hour([Time]) < 21 then "18-20" else 
        "21-23", type text),    
    InsertHourBin3Sort = Table.AddColumn(InsertHourBin3, "Hour Bin 3 Sort", each Number.IntegerDivide(Time.Hour([Time]), 3) + 1, Int32.Type),

    InsertHourBin2 = Table.AddColumn(InsertHourBin3Sort, "Hour Bin 2", each 
        if Time.Hour([Time]) < 2 then "0-1" else 
        if Time.Hour([Time]) < 4 then "2-3" else 
        if Time.Hour([Time]) < 6 then "4-5" else 
        if Time.Hour([Time]) < 8 then "6-7" else 
        if Time.Hour([Time]) < 10 then "8-9" else 
        if Time.Hour([Time]) < 12 then "10-11" else 
        if Time.Hour([Time]) < 14 then "12-13" else 
        if Time.Hour([Time]) < 16 then "14-15" else 
        if Time.Hour([Time]) < 18 then "16-17" else 
        if Time.Hour([Time]) < 20 then "18-19" else 
        if Time.Hour([Time]) < 22 then "20-21" else 
        "22-23", type text), 
    InsertHourBin2Sort = Table.AddColumn(InsertHourBin2, "Hour Bin 2 Sort", each Number.IntegerDivide(Time.Hour([Time]), 2) + 1, Int32.Type),
 
    // INSERT MINUTE BINS
    #"=== INSERT MINUTE BINS" = InsertHourBin2Sort,
    InsertMinuteBin30 = Table.AddColumn(#"=== INSERT MINUTE BINS", "Minute Bin 30", each 
        if Time.Minute([Time]) < 30 then "0-29" else 
        "30-59", type text),
    InsertMinuteBin30Sort = Table.AddColumn(InsertMinuteBin30, "Minute Bin 30 Sort", each Number.IntegerDivide(Time.Minute([Time]), 30) + 1, Int32.Type),

    InsertMinuteBin15 = Table.AddColumn(InsertMinuteBin30Sort, "Minute Bin 15", each 
        if Time.Minute([Time]) < 15 then "0-14" else 
        if Time.Minute([Time]) < 30 then "15-29" else 
        if Time.Minute([Time]) < 45 then "30-44" else 
        "45-59", type text),
    InsertMinuteBin15Sort = Table.AddColumn(InsertMinuteBin15, "Minute Bin 15 Sort", each Number.IntegerDivide(Time.Minute([Time]), 15) + 1, Int32.Type),

    InsertMinuteBin10 = Table.AddColumn(InsertMinuteBin15Sort, "Minute Bin 10", each 
        if Time.Minute([Time]) < 10 then "0-9" else 
        if Time.Minute([Time]) < 20 then "10-19" else 
        if Time.Minute([Time]) < 30 then "20-29" else 
        if Time.Minute([Time]) < 40 then "30-39" else 
        if Time.Minute([Time]) < 50 then "40-49" else 
        "50-59", type text),
    InsertMinuteBin10Sort = Table.AddColumn(InsertMinuteBin10, "Minute Bin 10 Sort", each Number.IntegerDivide(Time.Minute([Time]), 10) + 1, Int32.Type),

    InsertMinuteBin5 = Table.AddColumn(InsertMinuteBin10Sort, "Minute Bin 5", each 
        if Time.Minute([Time]) < 5 then "0-4" else 
        if Time.Minute([Time]) < 10 then "5-9" else 
        if Time.Minute([Time]) < 15 then "10-14" else 
        if Time.Minute([Time]) < 20 then "15-19" else 
        if Time.Minute([Time]) < 25 then "20-24" else 
        if Time.Minute([Time]) < 30 then "25-29" else 
        if Time.Minute([Time]) < 35 then "30-34" else 
        if Time.Minute([Time]) < 40 then "35-39" else 
        if Time.Minute([Time]) < 45 then "40-44" else 
        if Time.Minute([Time]) < 50 then "45-49" else 
        if Time.Minute([Time]) < 55 then "50-54" else 
        "55-59", type text),
    InsertMinuteBin5Sort = Table.AddColumn(InsertMinuteBin5, "Minute Bin 5 Sort", each Number.IntegerDivide(Time.Minute([Time]), 5) + 1, Int32.Type),
    #"=== END" = InsertMinuteBin5Sort

in
    #"=== END"
5 Likes

Hi Greg,

Thanks for sharing!

Hi there @Melissa , thank you for this post. Just want to be sure I have the correct values here for seconds? :-

Source = List.Times(#time(0, 0, 0), 86400, #duration(0,0,0,1)),

Hi @michellepace,

Yes you are correct this would result in a time table in seconds.

SecondsCount = 24*60*60, // 86400
Source = List.Times(#time(0,0,0), SecondsCount, #duration(0,0,0,1)),