cancel
Showing results for
Did you mean:
Regular Visitor

## Turn timeseries records into discrete events

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Turn timeseries records into discrete events

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-quotp...

Proud to be a Datanaut!

Imke Feldmann

7 REPLIES 7
Super User

## Re: Turn timeseries records into discrete events

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Regular Visitor

## Re: Turn timeseries records into discrete events

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

Super User

## Re: Turn timeseries records into discrete events

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-powe...

```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.

Proud to be a Datanaut!

Imke Feldmann

Highlighted
Regular Visitor

## Re: Turn timeseries records into discrete events

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 🙂

Super User

## Re: Turn timeseries records into discrete events

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-quotp...

Proud to be a Datanaut!

Imke Feldmann

Regular Visitor

## Re: Turn timeseries records into discrete events

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

Super User

## Re: Turn timeseries records into discrete events

Yes, of course.

Proud to be a Datanaut!

Imke Feldmann

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)