Forum Discussion
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...
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.
Yes indeed it's hard,but based on your suggestion I want the query to pick the names if both conditions are met,
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.