Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
96 | |
79 | |
65 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |