Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Choose values based on time range.

hello all, 

i'm facing an issue choosing values based on time range.

 

here's the situation i have an intial student plannings and teachers planning,knowing that each student is dispatched to a specific teacher,so i want to automate this process, so the query can check if the student is present during the presence of the teacher and selceted the names of student if the condition is true.

EX

Student A: start at 08:00 am and finishes at 12 pm, 

teacher of student A is present at 08:00-08:30.

 

Any help from your part will be highly apperciated.

 

Need Help
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Anonymous ,

    You can try:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKwsDIwACIg09AIzkQSBTKNwcxYnWglJ5CAJUKHAVYdljAdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, #"Stu start" = _t, #"Stu end" = _t, #"Tea start" = _t, #"Tea end" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Stu start", type time}, {"Stu end", type time}, {"Tea start", type time}, {"Tea end", type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ( [Stu start] <= [Tea start] and [Stu end] >= [Tea end])),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Student"})
in
    #"Removed Other Columns"

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    4 years ago

    Hard to give a specific expression without sample data or seeing your model, but basically you want an expression that checks that two conditions are met:

     

    the max teacher time is >= min student time && (and) min teacher time is <= max student time.

     

    Pat

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes indeed it's hard,but based on your suggestion I want the query to pick the names if both conditions are met,

      • v-yingjl's avatar
        v-yingjl
        Community Support
        4 years ago

        Hi Anonymous ,

        You can try:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKwsDIwACIg09AIzkQSBTKNwcxYnWglJ5CAJUKHAVYdljAdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, #"Stu start" = _t, #"Stu end" = _t, #"Tea start" = _t, #"Tea end" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Stu start", type time}, {"Stu end", type time}, {"Tea start", type time}, {"Tea end", type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ( [Stu start] <= [Tea start] and [Stu end] >= [Tea end])),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Student"})
in
    #"Removed Other Columns"

         

        Best Regards,
        Community Support Team _ Yingjie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.