Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
timpanister
Regular Visitor

Showing the hourly distribution classroom activity from student timetable data

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:

 

Studentdaystart timeend timeDur
A1Mon8:00:00 AM10:00:00 AM2:00
A1Mon12:00:00 PM1:00:00 PM1:00
A1Mon3:00:00 PM5:00:00 PM2:00
B1Mon8:00:00 AM10:00:00 AM2:00
B1Mon12:00:00 PM2:00:00 PM2:00
B1Mon3:00:00 PM4:00:00 PM1: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.

 

 

StudentDayTime1Time2Duration
A1Mon8:00:00 AM9:00:00 AM1:00
A1Mon9:00:00 AM10:00:00 AM1:00
A1Mon12:00:00 PM1:00:00 PM1:00
A1Mon3:00:00 PM4:00:00 PM1:00
A1Mon4:00:00 PM5:00:00 PM1:00
B1Mon8:00:00 AM9:00:00 AM1:00
B1Mon9:00:00 AM10:00:00 AM1:00
B1Mon12:00:00 PM1:00:00 PM1:00
B1Mon1:00:00 PM2:00:00 PM1:00
B1Mon3:00:00 PM4:00:00 PM1:00

 

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

 

Regards

Jenny

 

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

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"

1.PNG

For more details, please see the attachment.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xuding-msft
Community Support
Community Support

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"

1.PNG

For more details, please see the attachment.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.