cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mann Member
Member

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:

  1. Flag "0" a record when Start DateTime and End DateTime, time value is within groups of 5 min interval. (Example: Ind ID= 11,13,15,18,19,20). Groups of 5min are standard values like: 8:00:00 --> 8:05:00 --> 8:10:00 ..... -->8:55:00-->9:00:00
  2. Flag "1" if records have start time and end time same and on the standard values like (Ind ID= 12)
  3. Flag "1" if records have start time and end time across standard values like (Ind ID= 14,16,17)

 

Sample Data: (Flag column needs to be inserted in Power Query)

Ind IDStatus From TimeStatus To TimeFlag
1110/10/2019 8:02:00AM10/10/2019 8:02:00AM0
1210/10/2019 8:05:00AM10/10/2019 8:05:00AM1
1310/10/2019 8:02:00AM10/10/2019 8:04:00AM0
1410/10/2019 8:02:00AM10/10/2019 8:05:00AM1
1510/10/2019 8:05:02AM10/10/2019 8:05:02AM0
1610/10/2019 8:04:58AM10/10/2019 8:05:02AM1
1710/10/2019 8:05:00AM10/10/2019 8:05:02AM1
1810/10/2019 8:22:00AM10/10/2019 8:22:00AM0
1910/10/2019 8:32:00AM10/10/2019 8:34:00AM0
2010/10/2019 8:05:02AM10/10/2019 8:08:12AM0

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: Flagging DateTime records in Power Query with multiple conditions

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 "-"

 5 min.PNG

 

View solution in original post

7 REPLIES 7
edhans Super Contributor
Super Contributor

Re: Flagging DateTime records in Power Query with multiple conditions

Interesting puzzle. I am going to look at this this weekend, so someone may answer first, but I do have a question. Your criteria has conflicts. Row 11 satisfies both #1 and #2 criteria.

Mann Member
Member

Re: Flagging DateTime records in Power Query with multiple conditions

Hi @edhans

Row 11 doesnt satisfy Criteria #2 as it is not a standard value. By standard value I mean values like multiple of 5min e.g. 8:00, 8:05, 8:10.. etc.
It only satisfy Criteria #1 that is when start value and end value (whether it is same or not) is within standard values, in this case Row 11 is between 8:00 and 8:05.

I hope it clarifies.

Mann
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Flagging DateTime records in Power Query with multiple conditions

Hi @Mann ,

 

 

 

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#3" 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#2" else if Duration.Minutes([Status To Time]-[Status From Time])<=5 then 0 else "-"

 

 

 

I marked the ones as to the condition from your specs. Therefore 1#2 is 1 due to your number 2 requirement. As I said in my message to you I don't think 14,16,and 17 can be the same, as 8:05:00 has to be the beginning or the end of a segment. My logic says that it is at the beginning. end.



If beginning time minutes divided by 5 equals zero, and seconds are zero and if the two times match we solve #2 req.

If we roundup to the next five minutes, both times, and they are not the same, then we have crossed a boundary and #3 is solved.

Everything  else is zero unless the time duration > 5, then you get a "-" .

I added a row that starts at 8:00:00 for testing.
Flag.PNG

 


This may work for you. 
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 

Mann Member
Member

Re: Flagging DateTime records in Power Query with multiple conditions

Hi @Nathaniel_C 

 

Thanks for the reply on this. 

Flag of 14,16 and 17 has to be "1" only as per this rule:

"Flag "1" if records have start time and end time across standard values like (Ind ID= 14,16,17)"

 

Let me elaborate:

We need to show some chart at every 5 min interval (standard values like 8:00:00, 8:05:00, 8:10:00...) so when for any individual he is detected on these standard values he should be picked for calculation.

Therefore,

14 - when his end time detected on standard value

16- when he is detected at standard value between start time and end time

17- when his start time detected on standard value.

 

I hope this clarifies. 

I checked your code, it worked fine can we include record 14 also there?

 

Mann

 

Nathaniel_C Super Contributor
Super Contributor

Re: Flagging DateTime records in Power Query with multiple conditions

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 "-"

 5 min.PNG

 

View solution in original post

Mann Member
Member

Re: Flagging DateTime records in Power Query with multiple conditions

Thanks @Nathaniel_C..
This single custom column could handle all the conditions. Works great🙂
Nathaniel_C Super Contributor
Super Contributor

Re: Flagging DateTime records in Power Query with multiple conditions

Hi @Mann ,
You are welcome!

Nathaniel

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,043)