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.
¡Hola!
Muy nuevo usuario aquí y mi primer post.
Tengo horarios de estudiantes, por el cual cada registro muestra los nombres de los estudiantes y el horario semanal. Mi objetivo es trazar un gráfico que muestre el número de estudiantes que en una clase a la vez para cada día de la semana en una institución
Un fragmento de los registros aquí para el lunes:
Estudiante | Día | hora de inicio | tiempo de finalización | Duro |
A1 | Mi | 8:00:00 AM | 10:00:00 AM | 2:00 |
A1 | Mi | 12:00:00 PM | 1:00:00 PM | 1:00 |
A1 | Mi | 3:00:00 PM | 5:00:00 PM | 2:00 |
B1 | Mi | 8:00:00 AM | 10:00:00 AM | 2:00 |
B1 | Mi | 12:00:00 PM | 2:00:00 PM | 2:00 |
B1 | Mi | 3:00:00 PM | 4:00:00 PM | 1:00 |
Mi pensamiento simplista es: encontrar una manera de modificar los datos en algo como esto, que es dividir el tiempo en base por hora. Entonces podría trazar la suma del recuento de descuentos de los estudiantes para cada hora para mostrar los períodos pico y fuera de pico de la actividad en el aula.
Estudiante | Día | Tiempo1 | Tiempo2 | Duración |
A1 | Mi | 8:00:00 AM | 9:00:00 AM | 1:00 |
A1 | Mi | 9:00:00 AM | 10:00:00 AM | 1:00 |
A1 | Mi | 12:00:00 PM | 1:00:00 PM | 1:00 |
A1 | Mi | 3:00:00 PM | 4:00:00 PM | 1:00 |
A1 | Mi | 4:00:00 PM | 5:00:00 PM | 1:00 |
B1 | Mi | 8:00:00 AM | 9:00:00 AM | 1:00 |
B1 | Mi | 9:00:00 AM | 10:00:00 AM | 1:00 |
B1 | Mi | 12:00:00 PM | 1:00:00 PM | 1:00 |
B1 | Mi | 1:00:00 PM | 2:00:00 PM | 1:00 |
B1 | Mi | 3:00:00 PM | 4:00:00 PM | 1:00 |
Agradezco si pudiera obtener consejos aquí incluyendo cualquier otra estrategia que pueda lograr mi objetivo.
saludos
Jenny
Solved! Go to Solution.
Hola @timpanister ,
Tal vez esto pueda ayudarte.
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"
Para obtener más información, consulte el archivo adjunto.
Hola @timpanister ,
Tal vez esto pueda ayudarte.
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"
Para obtener más información, consulte el archivo adjunto.
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 |
---|---|
2 | |
1 | |
1 | |
1 | |
1 |
User | Count |
---|---|
2 | |
2 | |
2 | |
1 | |
1 |