Turn timeseries records into discrete events

[Anonymous]

Hi,

I'm hoping someone can assist. I have the time series data (recorded on a per second basis) in a Dataflow. and I would like to condense it  into discrete start up events using Power Query as this can then be used for ML purposes.

An actual event (or a job) will be the duration between X and Z. The start up event (i.e. the belt to get to running speed) is X to Y. The start up event does not need to consider the duration between Y and Z. Unfortunately, there aren't any status values as this would make life easier 🙂

The dataset is for a conveyor belt sensor readings. Each event needs to contain:

• Capture the start up time of a conveyor belt (column B)
• Capture the time (MIN of column A for each event) as start time
• Capture the time (Where column B stays static for 3 observations, capture the time at the 1st of the 3+ observations). This is the end time of that event
• Calculate the Average Belt Driver Current (column C)
• Calculate the Average Weight on the Belt (column D)
• Calculate the Average Belt Driver Temperature
• Calculate if the start up event was good or bad based on column B and D. In this scenario, If the start up event, is 72 and the average of the belt weight is 0, then that's a good start up. If the start up event is greater (90+) and the average of the belt weight is 0, then that's a bad start up. There are other scenarios as well. Again these are 2 examples. So in practice, I'll have to consider those.

Image highlights descriptions:

• 2 events - 1 red box and the other, blue. The first startup event is from row 3 to 75 (highlighted in yellow).  As you can see the entire event goes beyond row 75. From a start up event perspective, I have to end at value 72 (column B illustrated by the green dotted line as that value doesn't change until the end of the event. Start up events can vary up to 120 seconds. These are just 2 examples). 
• The second startup event is from row 5752 to 5829
• The arrows indicate the final result that I've after.
• The final column highlights if the start up event is good or bad.

I've attached links to a pbix and excel file containing the data.

Any assistance would be appreciated.

Thanks.

Pbix file

Excel file

[Greg_Deckler]

That will be an interesting one. @ImkeF may be able to help.

[Anonymous]

Thank you @Greg_Deckler . I appreciate your reply and tagging @ImkeF  🙂

[ImkeF]

Hi @Anonymous 

indeed, this is a challenging task. I've used a special grouping command to split up the relevant events, that I've described here: https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-power-query/

Table.Group(
                #"Removed Bottom Rows", 
                {"LastAccelTimeSec", "Previous.LastAccelTimeSec"}, 
                {   {"MinLocalTimeStamp", each  List.Min([localtimestamp]), type datetime}, 
                    {"Max", each  List.Max([LastAccelTimeSec]), type number}, 
                    {"All", each _}}, 
                0, 
                (x,y) => Number.From(x[LastAccelTimeSec] = y[LastAccelTimeSec] and  y[LastAccelTimeSec] < y[#"Previous.LastAccelTimeSec"])
        )

Please have in mind, that the first row of your table will be skipped. That row is currently mandatory for the solution, as it works with a "previous row". If your original data doesn't have that row to skip, you'd have to add that to your solution (the values in there don't matter - you just have to have a first row that will be skipped).

Please find the file with the full solution enclosed.

[Anonymous]

OMG @ImkeF  - That is so incredibly cool. I was pulling my hair out. Thank you so very much. Make me want to jump in head first into learning Power Query. 

I do have one final question, if I may, how would you calculate the rate of change for the last 5 data points and percentiles? Would that be in the same area you calculate the averages? From what I can see there isn't a percentile function.

PS: Thanks again. I can't tell you how happy you've made me 🙂

[ImkeF · Accepted solution]

Hi @Anonymous 

very pleased to hear 🙂

Not sure that I got your request right. I'd recommend to start a new thread and give some samples of the expected results (like you did in your original post).

Just take one table and then convert the solution into a function.

Here is a function for the Percentile in M btw: https://social.technet.microsoft.com/Forums/en-US/a57bfbea-52d1-4231-b2de-fa993d9bb4c9/can-the-quotpercentilequot-be-calculated-in-power-query?forum=powerquery 

[Anonymous]

Thank you. I will. Is it ok to tag you in it?

[ImkeF]

Yes, of course.