College Class Schedule Analysis

lanw 

I ended up taking the section start and end times and creating one row for each minute of the section. I did this by duplicating the columns for start and end times and changing them to a whole number format, then I added a column that extrapolated that by using a custom column with the following formula: {[SSRMEET_BEGIN_TIME]..[SSRMEET_END_TIME]} which creates a row for each value between the times. For example, a 1000 start to 1150 finish would give you all values between 1,000 and 1,150 for that section. You can then filter out any values where the last two characters are greater than 59 (to eliminate 1075, for example, which obviously isn’t a time value).

I also added a time table (see code below) to link the scheduling data to and used that table, along with counts to create a matrix visual with conditional formatting (see photo). I used a hierarchy so you can drill down from hour to half hour, quarter hours and even down to the minute. This allows you to see all of the overlap for the times. Let me know if this make sense!

Code for time table: You’ll need to add this into a blank query

let CreateTimeTable = () as table =>
 let
 // Similar to our CreateDateTable script, we start with the smallest unit of the dimension, minute
 // There are a fixed number of minutes in a day, so no need for parameters here
 // 525,600 minutes divided by 365 days in a year = 1440 minutes in a day. 
 // Who says we never learn from Broadway musicals?
 MinuteCount = 1440,
 // Now create a Time type list for a total of 1440 minutes, incrementing one minute at a time
 Source = List.Times(#time(0, 0, 0),MinuteCount, #duration(0,0,1,0)),
 // Turn that list into a one column table
 TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), 
 // Change that table's one column to type Time
 ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type time}}),
 // Rename column to Time
 RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Time"}}),
 // Start inserting columns for each unit of time to represent in the dimension
 InsertHour = Table.AddColumn(RenamedColumns, "Hour", each Time.StartOfHour([Time])),
 InsertMinute = Table.AddColumn(InsertHour, "Minute", each Time.Minute([Time])),
 ChangedTypeHour = Table.TransformColumnTypes(InsertMinute,{{"Hour", type time}}),
 // Creating levels in the hierarchy that might be useful for reporting. Omit if not useful to yours
 InsertQuarterHour = Table.AddColumn(ChangedTypeHour, "Quarter Hour", each if [Minute]<15 then [Hour] else if [Minute] < 30 then Value.Add([Hour],#duration(0,0,15, 0)) else if [Minute] < 45 then Value.Add([Hour],#duration(0,0,30, 0)) else Value.Add([Hour],#duration(0,0,45, 0))),
 ChangedTypeQtrHr = Table.TransformColumnTypes(InsertQuarterHour,{{"Quarter Hour", type time}}),
 ReorderedColumns = Table.ReorderColumns(ChangedTypeQtrHr,{"Time", "Hour", "Quarter Hour", "Minute"}),
 InsertHourNumber = Table.AddColumn(ReorderedColumns, "Hour Number", each Time.Hour([Time])),
 NextHour = Table.AddColumn(InsertHourNumber, "Next Hour", each Value.Add([Hour],#duration(0,1,0, 0))),
 NextQuarterHour = Table.AddColumn(NextHour, "Next Quarter Hour", each Value.Add([Quarter Hour],#duration(0,0,15, 0))),
 InsertPeriod = Table.AddColumn(NextQuarterHour, "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"),
 InsertPeriodSort = Table.AddColumn(InsertPeriod, "PeriodSort", 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),
 InsertTimeKey = Table.AddColumn(InsertPeriodSort, "TimeKey", each Time.ToText([Time], "HHmm"), type text)
 in
 InsertTimeKey
in
 CreateTimeTable