Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Image highlights descriptions:
I've attached links to a pbix and excel file containing the data.
Any assistance would be appreciated.
Thanks.
Solved! Go to 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
That will be an interesting one. @ImkeF may be able to help.
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
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 🙂
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
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