cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mans010 Frequent Visitor
Frequent Visitor

Group Consecutuve rows (timestamps)

Hi,

 

I have some trouble with grouping consecutive timestamps as 1. The Data is kind of alarmdata, so a row only appears when alarm is on. Every consecutive second it is "on" that means it is the same alarm that is still "on". So as you see:

timestamp 22:29:05 until 22:29:14 is consecutive. <---- one alarm = duration 9 seconds.  

timestamp 22:29:16 until 22:29:20 <--- other alarm. etc.

 

I would want to group the rows that have consecutive seconds. Therefor this table will consist of 4 different alarms. 
I think the best way is to creat a alarm starttime with the first timestamp per alarm and a column endtime.

timestampAlarm in WordBinary CodeBin in wordStatealarm/bitalarm descriptionalarm IDAlarm TypeSourceAlarm starttime

timestampAlarm in WordBinary CodeBin in wordStatealarm/bitalarm descriptionalarm IDAlarm TypeSourceAlarm starttime

1-5-2019 22:29:05152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:06152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:07152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:08152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:09152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:10152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:11152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:12152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:13152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:14152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:16152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:17152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:18152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:19152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:20152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:22152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:23152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:24152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:25152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:26152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:27152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:28152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:29152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:30152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:32152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:33152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:34152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:35152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:36152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:37152248Bin1511_15Warning B06 M17Not usedInternal 
1-5-2019 22:29:38152248Bin1511_15Warning B06 M17Not usedInternal 



Anybody that can help me with this?

2019-06-14 16_14_46-hydro. test - Power BI Desktop.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Group Consecutuve rows (timestamps)

@Mans010 

 

Please see attached file.

I added an Index Column then

I have added 2 custom columns "AlarmStart" and "AlarmEnd"

AlarmStart=let mytime=[timestamp]-#duration(0, 0, 0, 1),
myindex=[Index] in
try if Table.SelectRows(#"Added Index",each [Index]=myindex-1)[timestamp]{0}=mytime then null else [timestamp]
otherwise
[timestamp]
10 REPLIES 10
Super User
Super User

Re: Group Consecutuve rows (timestamps)

@Mans010 

 

Could you copy paste above data in the post (copiable format)?

Mans010 Frequent Visitor
Frequent Visitor

Re: Group Consecutuve rows (timestamps)

Hi Zubair,

 

I editted my post. Thanks for looking in to it

Super User
Super User

Re: Group Consecutuve rows (timestamps)

@Mans010 

 

Please see attached file.

I added an Index Column then

I have added 2 custom columns "AlarmStart" and "AlarmEnd"

AlarmStart=let mytime=[timestamp]-#duration(0, 0, 0, 1),
myindex=[Index] in
try if Table.SelectRows(#"Added Index",each [Index]=myindex-1)[timestamp]{0}=mytime then null else [timestamp]
otherwise
[timestamp]
Mans010 Frequent Visitor
Frequent Visitor

Re: Group Consecutuve rows (timestamps)

Thanks a lot! I see that your solution works excactly how I want it to work when opening you file.
But when copying it to my file, it gives a different result. (Just the same as the timestamp). Can't figure out what causes the difference.

2019-06-18 11_24_07-hydro. test - Power Query-editor.png

Super User
Super User

Re: Group Consecutuve rows (timestamps)

@Mans010 

 

May be your timestamp has milliseconds as well.

 

Could you send me your file?

Mans010 Frequent Visitor
Frequent Visitor

Re: Group Consecutuve rows (timestamps)

would like to, but çan't find the attach file mode? ( i am new on this forum sorry)

Super User
Super User

Re: Group Consecutuve rows (timestamps)

@Mans010 

 

You can upload it to onedrive or googledrive and share link here

 

Or you can email me as well

Mans010 Frequent Visitor
Frequent Visitor

Re: Group Consecutuve rows (timestamps)

Can you provide me of your email. Then I will send it to you. In your profile it state "null".

Super User
Super User

Re: Group Consecutuve rows (timestamps)

@Mans010 

 

Email: zubair@excelnaccess.com