Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you @Greg_Deckler . I appreciate your reply and tagging @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.

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

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 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

Yes, of course.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors