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

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

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.

 

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

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

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

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

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

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.

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

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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 198 members 2,435 guests
Please welcome our newest community members: