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 sp...
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.

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