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"