Forum Discussion
Mann
6 years agoResolver III
Flagging DateTime records in Power Query with multiple conditions
Hi Guys, I need to Flag some records with multiple conditions in Power Query. I am not finding the efficient way of doing it with minimal custom columns. Rules are: Flag "0" a record when Start...
- 6 years ago
Hi Mann ,
Try this, 14 now receives a 1, as do 12, 16, and 17 (I added a test for 21 which works as well)
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathanielif DateTime.From(Number.RoundUp(Number.From([Status From Time])*288,0)/288) <> DateTime.From(Number.RoundUp(Number.From([Status To Time])*288,0)/288) then "1" else if Number.Mod(Time.Minute([Status From Time]),5) = 0 and Time.Second([Status From Time])= 0 and [Status From Time]= [Status To Time] then "1" else if Number.Mod(Time.Minute([Status From Time]),5) = 0 and Time.Second([Status From Time])= 0 or Number.Mod(Time.Minute([Status To Time]),5) = 0 and Time.Second([Status To Time])= 0 then 1 else if Duration.Minutes([Status To Time]-[Status From Time])<=5 then 0 else "-"
Nathaniel_C
6 years agoCommunity Champion
Hi Mann ,
Try this, 14 now receives a 1, as do 12, 16, and 17 (I added a test for 21 which works as well)
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
if DateTime.From(Number.RoundUp(Number.From([Status From Time])*288,0)/288) <> DateTime.From(Number.RoundUp(Number.From([Status To Time])*288,0)/288) then "1"
else if Number.Mod(Time.Minute([Status From Time]),5)
= 0 and Time.Second([Status From Time])= 0 and [Status From Time]= [Status To Time] then "1"
else if Number.Mod(Time.Minute([Status From Time]),5)
= 0 and Time.Second([Status From Time])= 0 or Number.Mod(Time.Minute([Status To Time]),5)
= 0 and Time.Second([Status To Time])= 0 then 1
else if Duration.Minutes([Status To Time]-[Status From Time])<=5 then 0
else "-"
Mann
6 years agoResolver III
- Nathaniel_C6 years agoCommunity Champion
Hi Mann ,
You are welcome!Nathaniel