Showing the hourly distribution classroom activity from student timetable data

[timpanister]

Hi!

Very new user here and my first post.

I have student timetable, whereby each record shows student names and weekly schedule. My goal is to plot a chart that shows the number of students who in a class at any one time for each day of the week at an institution

A snippet of the records here for Monday:

Student	day	start time	end time	Dur
A1	Mon	8:00:00 AM	10:00:00 AM	2:00
A1	Mon	12:00:00 PM	1:00:00 PM	1:00
A1	Mon	3:00:00 PM	5:00:00 PM	2:00
B1	Mon	8:00:00 AM	10:00:00 AM	2:00
B1	Mon	12:00:00 PM	2:00:00 PM	2:00
B1	Mon	3:00:00 PM	4:00:00 PM	1:00

My simplistic thinking is: to find a way to modify the data into something like this, which is to breakdown the time into hourly basis.  Then I would be able to plot the sum of the discount count of students for each hour to show the peak and off-peak periods of classroom activity.

Student	Day	Time1	Time2	Duration
A1	Mon	8:00:00 AM	9:00:00 AM	1:00
A1	Mon	9:00:00 AM	10:00:00 AM	1:00
A1	Mon	12:00:00 PM	1:00:00 PM	1:00
A1	Mon	3:00:00 PM	4:00:00 PM	1:00
A1	Mon	4:00:00 PM	5:00:00 PM	1:00
B1	Mon	8:00:00 AM	9:00:00 AM	1:00
B1	Mon	9:00:00 AM	10:00:00 AM	1:00
B1	Mon	12:00:00 PM	1:00:00 PM	1:00
B1	Mon	1:00:00 PM	2:00:00 PM	1:00
B1	Mon	3:00:00 PM	4:00:00 PM	1:00

Appreciate if I could get advice here including any other strategies that can achieve my goal.

Regards

Jenny

[v-xuding-msft · Accepted solution]

Hi @timpanister ,

Maybe this can help you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lHyzc8DkhZWBgZApODoC+QYGiDzjIAspVgdFOWGRlAVAWD16Bx05cbICkyROXDDnUhzixMut2Bw0JWjuMUE0+WxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Student = _t, day = _t, #"start time" = _t, #"end time" = _t, Dur = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"day", type text}, {"start time", type time}, {"end time", type time}, {"Dur", type time}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Hour", each Time.Hour([Dur])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Hour", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each let a= [#"Hour"] in 
if a>=1
 then  List.Generate(()=>1,each _<= a,each _ +1)
 else  null),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type2", "Start Time.1", each let hour = [Hour], custom = [Custom] in 
if hour>1 and custom >1 
   then [start time]+#duration(0,1,0,0)
   else [start time]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom3", "End Time.1", each let hour = [Hour], custom = [Custom] in 
if hour>1 and custom <=1 
   then [end time]-#duration(0,1,0,0)
   else [end time]),
    #"Added Custom4" = Table.AddColumn(#"Added Custom1", "Duration", each #time(1,0,0)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"start time", "end time", "Dur", "Hour", "Custom"})
in
    #"Removed Columns"

For more details, please see the attachment.

[amitchandak]

@timpanister 

In this file, I have done some like this. I think across day too. please check the table create using the crossjoin.

https://www.dropbox.com/s/5wo5jhzl488zy69/Minute_between%20Hours.pbix?dl=0

[Greg_Deckler]

This one does not directly apply but I think can provide the concept of how to address this issue:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

Also, this might also help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306