cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ndesousa Regular Visitor
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.

 

CreateEventsFromTimeSeries.jpg

 

Pbix file

Excel file

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Turn timeseries records into discrete events

Hi @ndesousa 

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

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

7 REPLIES 7
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ndesousa Regular Visitor
Regular Visitor

Re: Turn timeseries records into discrete events

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

Super User
Super User

Re: Turn timeseries records into discrete events

Hi @ndesousa 

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.

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




ndesousa Regular Visitor
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 🙂

 

CreateEventsFromTimeSeries.jpg

Super User
Super User

Re: Turn timeseries records into discrete events

Hi @ndesousa 

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

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Highlighted
ndesousa Regular Visitor
Regular Visitor

Re: Turn timeseries records into discrete events

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

Super User
Super User

Re: Turn timeseries records into discrete events

Yes, of course.

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)