Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |