I am trying to figure out how to show time block useage for college course registration data. This would be most used as well as least used time blocks throughout each day of the week. The data are: Day, Start_Time, End_Time, CountofID (count of students in time block). Unfortunately not all time blocks are the same duration, thus lots of overlapping times.
I can plot a histogram of frequency by start time but because of the variability in timeblock duration, the plot is not particularlly acurate as to actual in-class or available times. Ideas?
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
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
