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

Aggregating Duration/Time data

I am pulling data from machines that are running.  The data will be recorded every second with a date stamp looking something like this...  eventually I am going to every 5 or 10 sec.. but the data is the same.

 

 

_MachineId  _TimeStamp               _Value    

612/5/2016 3:49:17 PM3
712/5/2016 3:49:17 PM0
1012/5/2016 3:49:17 PM1
312/5/2016 3:49:17 PM3
512/5/2016 3:49:17 PM0
612/5/2016 3:49:18 PM3
712/5/2016 3:49:18 PM0
1012/5/2016 3:49:18 PM1
312/5/2016 3:49:18 PM3
512/5/2016 3:49:18 PM0
612/5/2016 3:49:18 PM3
712/5/2016 3:49:18 PM0
1012/5/2016 3:49:18 PM1
312/5/2016 3:49:18 PM3
512/5/2016 3:49:18 PM0
612/5/2016 3:49:19 PM4

 

 

What I have not been able to find or figure out is how to aggreate across a day. 

 

In other words I would like it to end up something like this..   but hopefully graphical.  The graphical is easy once the data is there correctly. 

 

Machine    time                Len        state

3               7am - 7:36      36 Min      1

3                7:36 - 7:38      2  Min      3

3                7:38  - 8:00    22 Min       1

 

 The idea is to be able to drill down to specific points in time where a problem exists (machine down) and through other data linked in, view alarms, operator input, etc.   But I can't get this part even started.

 

thanks in advance... and if someone could let me know if I did it right by replying to a common thread vs creating new I would appreciate it.

4 REPLIES 4
Steve_Wheeler Established Member
Established Member

Re: Aggregating Duration/Time data

@nblair

Your scenario has some interesting wrinkles in it - a sort of streaming analytics with status updates every 1-10 seconds.  I suspect you'll have some other issues around perfromance, refresh, edge cases such as machines that stop reporting etc.

 

To get started, and assuming your Machines don't change state on every read, you could add a "Change_State_Flag" column in Power QUery as you import the data - then you can easily filter those (many fewer?) records that indicate a change in the "_Value" or a different "_MachineID" - e.g.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3c5LCsAgDEXRrUjGgonxE91DofPg/rfRVqdK5p0+DryrCgU8UAw5RKTiuKfWqbr7emeG4RXqGeAEhGdBU7D1ka2PXaVYlWJWilUpVqX8o7ItkDaA8icSroPxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [_MachineId = _t, _TimeStamp = _t, _Value = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"_MachineId", Int64.Type}, {"_TimeStamp", type datetime}, {"_Value", Int64.Type}}),
    SortedRows = Table.Sort(ChangedType,{{"_MachineId", Order.Ascending}, {"_TimeStamp", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1),
    AddedChangedStateFlag = Table.AddColumn(AddedIndex, "Changed_State_Flag", each if [Index] = 0 or ([_MachineId] <> AddedIndex[_MachineId]{[Index]- 1} or [_Value] <> AddedIndex[_Value]{[Index]- 1}) then 1 else 0 ),
    SetFlagAsNumber = Table.TransformColumnTypes(AddedChangedStateFlag,{{"Changed_State_Flag", Int64.Type}})
in
    SetFlagAsNumber

Then you could add some Measures for the Start and End Timestamps on a change of state (e.g. when does Machine 1 go from Value 3 to 4 and when does it then change from 4 for another Value).  Then you can get a Duration (in seconds for your current test data) - e.g.

 

State Start Timestamp =
CALCULATE (
    MIN ( Table1[_TimeStamp] ),
    FILTER (
        Table1,
        Table1[Changed_State_Flag] = 1
            && Table1[_Value] = MAX ( Table1[_Value] )
            && Table1[_MachineID] = MAX ( Table1[_MachineId] )
            && Table1[_TimeStamp] = MAX ( Table1[_TimeStamp] )
    )
)
State End Timestamp =
CALCULATE (
    MIN ( Table1[_TimeStamp] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Changed_State_Flag] = 1
            && Table1[_Value] <> MAX ( Table1[_Value] )
            && Table1[_MachineID] = MAX ( Table1[_MachineId] )
            && Table1[_TimeStamp] > MAX ( Table1[_TimeStamp] )
    )
)
State Duration Seconds =
IF (
    ISBLANK ( [State Start TimeStamp] ) || ISBLANK ( [State End Timestamp] ),
    BLANK (),
    ( [State End TimeStamp] - [State Start Timestamp] )
        * 24
        * 60
        * 60
)

Add them to a table etc. with a filter on State_Change_Flag  = 1, and you'll get close to what you were proposing though I'm not sure how to present it graphically.

 

nblair Frequent Visitor
Frequent Visitor

Re: Aggregating Duration/Time data

Not sure if there is a tool in PowerBI to do it.. but I was thinking something like a timeline format in Project would be a neat way to show a day.   

 

 A vertical stacked column would work too, except that I think it will combine all the data into single segments instead of by time.  

 

I'm trying to keep it as standard as possible in regards to using standard toolsets.  

 


As for colidating the data, I'll try to work through your suggestion and see what I can get put together...

 

thanks for the help

Highlighted
nblair Frequent Visitor
Frequent Visitor

Re: Aggregating Duration/Time data

Thanks..

 

gonna take me a bit to absorb this, but I think I've got it.

 

At least initially, I'm not going to even try to keep the visuals of the data up to date with current machine status.  When I figure it out, I'll be able to change this a bit and go to 'unsolicited data' from the fanuc controller - which will allow me to request data far less often as the machine will send an update.  I have to learn that first tho... Smiley Happy

 

In the meantime, I can display up to the last hour or so and that will be a lot better than how blind I am now.

 

 

Steve_Wheeler Established Member
Established Member

Re: Aggregating Duration/Time data

I hope the ideas above help.

 

Cheers,

Steve.