I’ve been struggling with this for a few days and am hoping the community can provide some helpful suggestions!
The challenge is to create a report from data which provides a timestamp,machine status and plant power consumption. The plant has three machines which consume a lot of electricity and do not run at the same time, but at (multiple) intervals throughout a workday.
The currently running machine is obtained through SCADA and power consumption is obtained from electrical metering equipment which creates a new row every few seconds to minutes (depending on current useage). Quite a few rows will be added each day.
Ideally, this would be displayed as a daily timeline visual.
I’ve included a XLS with worksheets that contain the sample dataset and desired results. Any help would be greatly appreciated! Thanks…
Dataset.xlsx (20.5 KB)
You could use the groupby feature of power query, the UI will allow you to return a layout like the tab, see the query next to the table for reference.
Thank you for the reply! Perhaps I wasn’t clear enough in my original question.
I can’t use Groupby because the machines cycle on and off during the workday and I need the consumption and duration of each -on- event. I tried grouping by machine and hour but that strategy yeilds incorrect calculations due to the events crossing hours.
Thanks again. I very much appreciate any suggestions!
Your sample data only includes records with Melt Status == “Running”. If all machines are in the same melt status, the example from
works.
You could also create this table:
with a simple measure like Total Power Consumed = SUM('Dataset'[Power Consumed]) and a Machine dimension from a dim table you create in your modeling steps.
However, you said
That sounds simple. Can you provide sample data that captures the cycling on and off?
There is also the groupkind.local arguement, and the posibility to run a function to determin the cut off for each group, as HuuferD has suggested if you provide sample data of the cycling it would be possible to give a better answer, its possible you could do it through ETL and DAX then you can see which works best.
Dataset 2.xlsx (60.8 KB)
If your data is structured in a way that it is the consecutive items that have the same machine ID grouped you can use group by with groupkind.local, if the requirement is more complex you would need to work out how to identify the switch between the grouping, if it is based off order in the data then its not so bad.
Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.
If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
Thank you!! I wasn’t aware of the groupkind.local function. Just to be clear, I would first group by machine and then add the groupkind.local to that step?
Thanks again. I apoligize for the late reply…I was out for a few days!
Yes, do your grouping by machine, then add a comma to the expression at the end and add groupkind.local, it should have intellisense and will group by the changes in id
