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,
This is an update to a request I posted a couple of days back and the fabulous @ImkeF came back with a great solution. I now have a couple of other tricky (to me anyway) requirements that have to be factored into the final dataset.
Background
I have 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.
Requirements (resolved per previous request by @ImkeF )
The dataset is for a conveyor belt sensor readings. Each event needs to contain:
New Requirements
Image highlights descriptions:
I've attached links to a pbix and excel file containing the data.
As you can see from this image, I've taken @ImkeF 's solution, extended it to include quartile and percentile (I think I have got it implemented correctly?). This missing piece is the Average percentage change.
Any assistance would be appreciated.
Solved! Go to Solution.
Hi @Anonymous ,
Do you want to get your output with Power Query only?
If so, I'm afraid that I'm not good at power query. Hope @ImkeF would give further help.
If dax is also helpful, please share your desired output so that we could give further advice on it.
Best Regards,
Cherry
Hi @Anonymous ,
Do you want to get your output with Power Query only?
If so, I'm afraid that I'm not good at power query. Hope @ImkeF would give further help.
If dax is also helpful, please share your desired output so that we could give further advice on it.
Best Regards,
Cherry
Thanks for your reply. Ideally in Power Query. If it was DAX, it would have to be as a Summary table. I say Power Query because the final output will be a computed entity over a dataflow.
Hi @ImkeF
I managed to work out the average for the last 5 records. I don't know if it is as elegant as you would have done it - and maybe you will see something wrong with it. If you do, please let me know 🙂
I had to:
This image shows the M query
This is the final result. The table with the purple header is the result of the above Power Query. It matches the table with the green header, which was based on the required result from the Excel file.
This is the link to the pbix file.
Thanks for your help 🙂
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.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |