Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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]

Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

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


Regards
Zubair

Please try my custom visuals

@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]

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

@Anonymous 

 

May be your timestamp has milliseconds as well.

 

Could you send me your file?


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

Anonymous
Not applicable

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


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

Hi Zubair,

 

I editted my post. Thanks for looking in to it

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.