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

[I_dont_know_M]

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

[v-shex-msft]

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(
        #"Added Index",
        "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}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try ([#"Power (kW)"] + #"Added Index"[#"Power (kW)"]{[Index] - 1}) /2*([#"Duration (h)"]-#"Added Index"[#"Duration (h)"]{[Index]- 1}) otherwise 0)
in
    #"Added Custom"

Operators - PowerQuery M | Microsoft Learn

Regards,

Xiaoxin Sheng

[I_dont_know_M]

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:

[I_dont_know_M]

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:

[v-shex-msft · Accepted solution]

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(
        #"Added Custom",
        "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(
        #"Added Custom1",
        "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}}
    ),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(
        #"Added Index",
        "Raw",
        each
            try
                ([#"Power (kW)"] + #"Added Index"[#"Power (kW)"]{[Index] - 1}) / 2 * (
                    [#"Duration (h)"] - #"Added Index"[#"Duration (h)"]{[Index] - 1}
                ) otherwise 0
    ),
    #"Added Custom1" = Table.AddColumn(
        #"Added Custom",
        "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)"]
                    )
                )
    ),
    #"Added Custom2" = Table.AddColumn(
        #"Added Custom1",
        "Rolling result",
        each let currIndex = [Index] in List.Sum(Table.SelectRows(#"Added Custom1", each [Index] <= currIndex)[Raw])
    )
in
    #"Added Custom2"

Regards,

Xiaoxin Sheng

[I_dont_know_M]

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.

[v-shex-msft]

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

[I_dont_know_M]

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.