Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
6 | 12/5/2016 3:49:17 PM | 3 |
7 | 12/5/2016 3:49:17 PM | 0 |
10 | 12/5/2016 3:49:17 PM | 1 |
3 | 12/5/2016 3:49:17 PM | 3 |
5 | 12/5/2016 3:49:17 PM | 0 |
6 | 12/5/2016 3:49:18 PM | 3 |
7 | 12/5/2016 3:49:18 PM | 0 |
10 | 12/5/2016 3:49:18 PM | 1 |
3 | 12/5/2016 3:49:18 PM | 3 |
5 | 12/5/2016 3:49:18 PM | 0 |
6 | 12/5/2016 3:49:18 PM | 3 |
7 | 12/5/2016 3:49:18 PM | 0 |
10 | 12/5/2016 3:49:18 PM | 1 |
3 | 12/5/2016 3:49:18 PM | 3 |
5 | 12/5/2016 3:49:18 PM | 0 |
6 | 12/5/2016 3:49:19 PM | 4 |
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.
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.
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... 🙂
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.
I hope the ideas above help.
Cheers,
Steve.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |