Group Consecutuve rows (timestamps)

[Anonymous]

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:05	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:06	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:07	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:08	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:09	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:10	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:11	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:12	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:13	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:14	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:16	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:17	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:18	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:19	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:20	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:22	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:23	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:24	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:25	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:26	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:27	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:28	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:29	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:30	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:32	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:33	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:34	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:35	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:36	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:37	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal
1-5-2019 22:29:38	1	52248	Bin15	1	1_15	Warning B06 M1	7	Not used	Internal

Anybody that can help me with this?

[Zubair_Muhammad]

@Anonymous 

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

[Zubair_Muhammad · Accepted solution]

@Anonymous 

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]

[Anonymous]

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.

[Zubair_Muhammad]

@Anonymous 

May be your timestamp has milliseconds as well.

Could you send me your file?

[Anonymous]

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

[Zubair_Muhammad]

@Anonymous 

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

Or you can email me as well

[Anonymous]

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

[Anonymous]

Hi Zubair,

I managed to solve the last problem myself regarding that the timestamp was also in milliseconds. This solved my problem I e-mailed you. 

The other issue that it needs to see the alarms seperatly is still an issue though. Don't know if you have ideas on this aswell?

Thanks

[Zubair_Muhammad]

@Anonymous 

Email: zubair@excelnaccess.com

[Anonymous]

Hi Zubair,

I editted my post. Thanks for looking in to it