Calculate Energy consumption from power reading every two seconds

Good day caseki,

If the power reading is p at the end of a 2 second period you may assume that p is a reasonable approximation to the power throughout the prior 2 seconds. If p is in kW, the energy expended in those 2 seconds is p*(2/3600) kWh (as 2 seconds is 2/3600th of an hour).

 

The following steps will give you the energy per day...essentially you integrate the area under the chart of p v time.

1. Add an index column, starting at 0.
2. Calculate the energy consumed in each period.
   
   1. Calculate the length of the period in hours (this is where the index column comes in - you take the timestamp in the current row and subtract the timestamp in the row with index of one less). Do not assume all periods are 2 seconds as in the real world readings get missed. I discard the first row as it does not have a prior row - this hardly matters in the course of a day.
   2. Multiply by the power
3. Transform the timestamp column to date only.
4. Group by date, summing energy.

If you have days and days of energy this could be an expensive calculation. In that circumstance you could try out a rougher approximation where you group by minute and take an average (mean or median) power for the minute and then integrate from there. This would reduce the number of rows by a factor of 30.

Hope this helps.

 

The following code uses your data as a starting point.

let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"fZZJbm0hDES38pUxUtzhJluJsv9t/KLJDOcNLyVTmOL4fX9/KH0SfwqJ/WP+Uv+a9jE+6ONnvNb8j7Xs1uKL6I81wZrPIUSi2miWJ+dhNGVyo1ne3AaJlFqjWR6nDmUl87eGl1en4RU5o9HI0RAt043GzrlYrLTxzMtz6DDGr5Esy0EjmafXWyPbcqKF7DEbzbIcMmZVRHMsWZYDbU4hb44l2/IcJkVdmW25hno196B0zl0k0W2kcqpwlVRzKF2Ga6I5ytTVWYYrhptJlwtdjiuHZ3o2dWx5xi7ooIQ0GtkahLCqtGmPLdPMMazK20rLNYsMJN68q5S/lqKkC8fcvkUHhRk1281tnAkR0kLk3qJtXHwZn9mJtnGlMVmiC/VmBdYHNgt/esoDDZYahOuV5+ny0GNVciKn53Z58IF3CH7geE/jefjBtsJUrJ1oG580LPDxmco8BGHFdlbRVdoIwZMeOvEEukrbuPFgYo2mBRsiDDripVh02x3jSGYiBo1oc4QtkAKPdzLzgITN4Cn1/VjykITdR6Zp1wI5xmMxqbhpwYbJqjRtTns+hDw8YQcsHPBvPG2gMKgdgH81zdxEQSYXAz2bPOmv8SSybrftGzCVqvTnrMnLlASVMY7mE055oYJKLhazq3R8y8hyl+bqDlQi0XBoOtExjreJ2cZNww9UMJYgojed80IFbUJM6o2CvFDB6TJSu8wdqCQGLtg7mzgdqCAEmCpWzz7VhcoaubiW92OpCxUMVLKp77lSFypoAZghb2TWhQoqMQbq+59CXaggc2DTfOayLlNwv+i3PrtUFynLUYh2jg5SJqazzJTnpdRFCkToI8/n9dZFyn4qmCsI789/",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [TimeColDL_09_POWER_MILL = _t, #"(blank)" = _t]
),
#"Renamed Columns" = Table.RenameColumns(
Source, {{"(blank)", "Power"}, {"TimeColDL_09_POWER_MILL", "Timestamp"}}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Renamed Columns", {{"Timestamp", type datetime}, {"Power", Int64.Type}}
),

//These are the important steps...

#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Energy" = Table.AddColumn(
  #"Added Index",
  "Energy",
  each Duration.TotalHours([Timestamp] - #"Added Index"{List.Max({[Index] - 1, 0})}[Timestamp]) * [Power], type number),
#"Extracted Date" = Table.TransformColumns(#"Added Energy", {{"Timestamp", DateTime.Date, type date}}),
#"Grouped Rows" = Table.Group(
#"Extracted Date", {"Timestamp"}, {{"Energy for Day", each List.Sum([Energy]), type number}} )
in
  #"Grouped Rows"