cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Numerical integration in M language? I need real-time energy consumption in kWh from power kW and h

I have about 50k rows of raw data containing time stamps at 5 second interval, and corresponding votage and current. I was able to convert in Power Query the duration in hour and power in kW. Now I need a new column that tracks the real-time energy consumption in kWh, i.e., numerical integration of power over time from start to each row. I was able to do this using the trapezoidal rule in Excel after loading the data, using formula like ((power current row) + (power previous row))/2 x (time current row - time previous row) + energy privous row, fill formula takes less than a second. The result looks like this

Now, I wonder if I can do this in M language creating a column in the query transformation. Please share any ideas thank you very much.

Data sample :

 Duration (h) Power (kW) 0 0 0.001388889 0.108333333 0.002777778 0.10625 0.004166667 0.104166667 0.005555556 0.10625 0.006944444 0.104166667 0.008333333 0.104166667 0.009722222 0.104166667 0.011111111 0.104166667 0.0125 0.104166667 0.013888889 0.104166667 0.015277778 0.104166667 0.016666667 0.104166667 0.018055556 0.104166667 0.019444444 0.104166667 0.020833333 0.104166667 0.022222222 0.104166667 0.023611111 0.104166667 0.025 0.104166667 0.026388889 0.104166667 0.027777778 0.104166667 0.029166667 0.104166667 0.030555556 0.104166667 0.031944444 0.104166667 0.033333333 0.104166667 0.034722222 0.104166667 0.036111111 0.104166667 0.0375 0.104166667 0.038888889 0.104166667 0.040277778 0.104166667 0.041666667 0.104166667 0.043055555 0.104166667 0.044444445 0.104166667 0.045833333 0.104166667 0.047222222 0.104166667 0.048611111 0.104166667 0.05 0.104166667 0.051388889 0.10625 0.052777778 0.104166667 0.054166667 0.104166667 0.055555556 0.104166667 0.056944444 0.104166667 0.058333333 0.104166667 0.059722222 0.104166667 0.061111111 0.104166667 0.0625 0.104166667 0.063888889 0.104166667 0.065277778 0.104166667 0.066666667 0.104166667 0.068055556 0.104166667 0.069444444 0.104166667 0.070833333 0.10625 0.072222222 0.104166667 0.073611111 0.104166667 0.075 0.104166667 0.076388889 0.104166667 0.077777778 0.104166667 0.079166667 0.104166667 0.080555556 0.104166667 0.081944444 0.104166667 0.083333333 0.104166667 0.084722222 0.104166667 0.086111111 0.104166667 0.0875 0.10625 0.088888889 0.10625 0.090277778 0.104166667 0.091666667 0.104166667 0.093055556 0.10625 0.094444445 0.104166667 0.095833333 0.104166667 0.097222222 0.104166667 0.098611111 0.104166667 0.1 0.104166667 0.101388889 0.10625 0.102777778 0.104166667 0.104166667 0.104166667 0.105555556 0.104166667 0.106944444 0.104166667 0.108333333 0.104166667 0.109722222 0.104166667 0.111111111 0.104166667 0.1125 0.104166667 0.113888889 0.104166667 0.115277778 0.104166667 0.116666667 0.104166667 0.118055556 0.104166667 0.119444444 0.104166667 0.120833333 0.104166667 0.122222222 0.104166667 0.123611111 0.104166667 0.125 0.104166667 0.126388889 0.104166667 0.127777778 0.104166667 0.129166667 0.104166667 0.130555556 0.104166667 0.131944444 0.104166667 0.133333333 0.104166667 0.134722222 0.104166667 0.136111111 0.104166667
1 ACCEPTED SOLUTION
Community Support

HI @I_dont_know_M,

What type of cumulative are you means? Calculate with the rolling total of the field values or just summary the calculated results?

For the first one, these calculations should not be suitable to use operators, you may need to nest M query functions for these calculations.

``````    #"Added Custom1" = Table.AddColumn(
"Rolling Field Value",
each
let
tb = #"Added Custom", _index = [Index]
in
(
List.Sum(Table.SelectRows(tb, each [Index] <= _index)[#"Power (kW)"]) + List.Sum(
Table.SelectRows(tb, each [Index] <= _index - 1)[#"Power (kW)"]
)
) / 2 * (
List.Sum(Table.SelectRows(tb, each [Index] <= _index)[#"Duration (h)"]) - List.Sum(
Table.SelectRows(tb, each [Index] <= _index - 1)[#"Duration (h)"]
)
)
)``````

For the second one, you can simply add a new column to summary previous field calculation result based on current index.

``````    #"Added Custom2" = Table.AddColumn(
"Rolling result",
each let currIndex = [Index] in List.Sum(Table.SelectRows(#"Added Custom1", each [Index] <= currIndex)[Raw])
)``````

Result:

Full query:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZZtroMwDATv0t9VlU2wk5yl6v2v8fgIPEB4LFUqrBDt1Ez3+32l13t+/d7zu09KKm2evpz7KLWyzpHmukwbqWc7kkk+Tx3JfrSnto4/XOd9Wia4br//c9prXuY51ZggnT/Cc7B+/wPAPbV8BnBP3QGCWjpDuKcbhwBEHiSCdEyQFgcQEYfsBThsexBxyJ2WoSQDDkW0EKXQQpSJFmJgCDiUGoDY9iECMSVaiO0wAjENEkG6TZQaLcTgEICYGi1EcEO7iuH0CFumXTAUgxntgqEcDOVgKAdHOXj0UDjKwVEOjnJwlIOjHOpVDqefpqIYKooheh4qiqGiGCqKoaEYGoqhoRgaiqGhGNoB4gS2XaVwSjoKoaMQeknB32RHGXSUQUcZdJJBcDZFLlAiF/wfP6fkAmFREBYFYVEQFgVFRUFYFIRFQVgUhEVBWBSERUFYFIRFIeSARUFYFIRFQVgUhEVBWBSERUFRUfj9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Duration (h)" = _t, #"Power (kW)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(
Source, {{"Duration (h)", type number}, {"Power (kW)", type number}}
),
"Raw",
each
try
([#"Power (kW)"] + #"Added Index"[#"Power (kW)"]{[Index] - 1}) / 2 * (
[#"Duration (h)"] - #"Added Index"[#"Duration (h)"]{[Index] - 1}
) otherwise 0
),
"Rolling Field Value",
each
let
tb = #"Added Custom", _index = [Index]
in
(
List.Sum(Table.SelectRows(tb, each [Index] <= _index)[#"Power (kW)"]) + List.Sum(
Table.SelectRows(tb, each [Index] <= _index - 1)[#"Power (kW)"]
)
) / 2 * (
List.Sum(Table.SelectRows(tb, each [Index] <= _index)[#"Duration (h)"]) - List.Sum(
Table.SelectRows(tb, each [Index] <= _index - 1)[#"Duration (h)"]
)
)
),
"Rolling result",
each let currIndex = [Index] in List.Sum(Table.SelectRows(#"Added Custom1", each [Index] <= currIndex)[Raw])
)
in

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
7 REPLIES 7
Community Support

HI @I_dont_know_M,

You can add an index column(start from 0 with 1 interval) to your table to help remark rows, then you can use the current row value and index to get previous row value to calculate:

``````    #"Added Custom" = Table.AddColumn(
"Custom",
each
try
([#"Power (kW)"] + #"Added Index"[#"Power (kW)"]{[Index] - 1}) / 2 * (
[#"Duration (h)"] - #"Added Index"[#"Duration (h)"]{[Index] - 1}
) otherwise 0
)``````

Full query:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZZtroMwDATv0t9VlU2wk5yl6v2v8fgIPEB4LFUqrBDt1Ez3+32l13t+/d7zu09KKm2evpz7KLWyzpHmukwbqWc7kkk+Tx3JfrSnto4/XOd9Wia4br//c9prXuY51ZggnT/Cc7B+/wPAPbV8BnBP3QGCWjpDuKcbhwBEHiSCdEyQFgcQEYfsBThsexBxyJ2WoSQDDkW0EKXQQpSJFmJgCDiUGoDY9iECMSVaiO0wAjENEkG6TZQaLcTgEICYGi1EcEO7iuH0CFumXTAUgxntgqEcDOVgKAdHOXj0UDjKwVEOjnJwlIOjHOpVDqefpqIYKooheh4qiqGiGCqKoaEYGoqhoRgaiqGhGNoB4gS2XaVwSjoKoaMQeknB32RHGXSUQUcZdJJBcDZFLlAiF/wfP6fkAmFREBYFYVEQFgVFRUFYFIRFQVgUhEVBWBSERUFYFIRFIeSARUFYFIRFQVgUhEVBWBSERUFRUfj9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Duration (h)" = _t, #"Power (kW)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration (h)", type number}, {"Power (kW)", type number}}),
in

Operators - PowerQuery M | Microsoft Learn

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Thanks a lot Xiaoxin, it's an honour to get your help. I read your code and tried on my end, but realized that for each row it calculates the incremental change in energy (kWh), I wanted the total energy from the beginning to each row. Is is possible for M language to calculate a sum from the beginning of a column to each row of the same column and put the results in a new column?

The plot of the code you provided looks like this:

Regular Visitor

Thanks a lot Xiaoxin, it's an honour to get your help. I read your code and tried on my end, but realized that for each row it calculates the incremental change in energy (kWh), I wanted the total energy from the beginning to each row. Is is possible for M language to calculate a sum from the beginning of a column to each row of the same column and put the results in a new column?

The plot of the code you provided looks like this:

Community Support

HI @I_dont_know_M,

What type of cumulative are you means? Calculate with the rolling total of the field values or just summary the calculated results?

For the first one, these calculations should not be suitable to use operators, you may need to nest M query functions for these calculations.

``````    #"Added Custom1" = Table.AddColumn(
"Rolling Field Value",
each
let
tb = #"Added Custom", _index = [Index]
in
(
List.Sum(Table.SelectRows(tb, each [Index] <= _index)[#"Power (kW)"]) + List.Sum(
Table.SelectRows(tb, each [Index] <= _index - 1)[#"Power (kW)"]
)
) / 2 * (
List.Sum(Table.SelectRows(tb, each [Index] <= _index)[#"Duration (h)"]) - List.Sum(
Table.SelectRows(tb, each [Index] <= _index - 1)[#"Duration (h)"]
)
)
)``````

For the second one, you can simply add a new column to summary previous field calculation result based on current index.

``````    #"Added Custom2" = Table.AddColumn(
"Rolling result",
each let currIndex = [Index] in List.Sum(Table.SelectRows(#"Added Custom1", each [Index] <= currIndex)[Raw])
)``````

Result:

Full query:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZZtroMwDATv0t9VlU2wk5yl6v2v8fgIPEB4LFUqrBDt1Ez3+32l13t+/d7zu09KKm2evpz7KLWyzpHmukwbqWc7kkk+Tx3JfrSnto4/XOd9Wia4br//c9prXuY51ZggnT/Cc7B+/wPAPbV8BnBP3QGCWjpDuKcbhwBEHiSCdEyQFgcQEYfsBThsexBxyJ2WoSQDDkW0EKXQQpSJFmJgCDiUGoDY9iECMSVaiO0wAjENEkG6TZQaLcTgEICYGi1EcEO7iuH0CFumXTAUgxntgqEcDOVgKAdHOXj0UDjKwVEOjnJwlIOjHOpVDqefpqIYKooheh4qiqGiGCqKoaEYGoqhoRgaiqGhGNoB4gS2XaVwSjoKoaMQeknB32RHGXSUQUcZdJJBcDZFLlAiF/wfP6fkAmFREBYFYVEQFgVFRUFYFIRFQVgUhEVBWBSERUFYFIRFIeSARUFYFIRFQVgUhEVBWBSERUFRUfj9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Duration (h)" = _t, #"Power (kW)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(
Source, {{"Duration (h)", type number}, {"Power (kW)", type number}}
),
"Raw",
each
try
([#"Power (kW)"] + #"Added Index"[#"Power (kW)"]{[Index] - 1}) / 2 * (
[#"Duration (h)"] - #"Added Index"[#"Duration (h)"]{[Index] - 1}
) otherwise 0
),
"Rolling Field Value",
each
let
tb = #"Added Custom", _index = [Index]
in
(
List.Sum(Table.SelectRows(tb, each [Index] <= _index)[#"Power (kW)"]) + List.Sum(
Table.SelectRows(tb, each [Index] <= _index - 1)[#"Power (kW)"]
)
) / 2 * (
List.Sum(Table.SelectRows(tb, each [Index] <= _index)[#"Duration (h)"]) - List.Sum(
Table.SelectRows(tb, each [Index] <= _index - 1)[#"Duration (h)"]
)
)
),
"Rolling result",
each let currIndex = [Index] in List.Sum(Table.SelectRows(#"Added Custom1", each [Index] <= currIndex)[Raw])
)
in

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Thanks Xiaoxin, your code makes sense, what I needed was the rolling total of incremental energy consumption so it's the second option.

However, I don't know why, but even calculating the "Raw" column took 20 min, and calculating the "rolling total" is ongoing past 30 min.

Community Support

HI @I_dont_know_M,

In fact, M query function normally used to sharpen table structures a transforming data, they are not suitable to achieve complex calculations.(they can work but will process with poor performance when do some looping calculating across whole table records)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Thanks Xiaoxin, I tried today with 3000 rows and it took 1h 15min to get to load 1500 rows, so this is definitely not practical.

What is the proper way of doing such calculation other than Excel functions, if you don't mind enlighten me a little bit.