Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.