Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |