Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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
Could you copy paste above data in the post (copiable format)?
@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]
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.
@Anonymous
May be your timestamp has milliseconds as well.
Could you send me your file?
would like to, but çan't find the attach file mode? ( i am new on this forum sorry)
@Anonymous
You can upload it to onedrive or googledrive and share link here
Or you can email me as well
Can you provide me of your email. Then I will send it to you. In your profile it state "null".
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
@Anonymous
Email: zubair@excelnaccess.com
Hi Zubair,
I editted my post. Thanks for looking in to it
User | Count |
---|---|
123 | |
96 | |
89 | |
73 | |
63 |
User | Count |
---|---|
138 | |
115 | |
111 | |
98 | |
95 |