Skip to main content
cancel
Showing results for 
Search instead 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

Reply
I_dont_know_M
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

Picture1.png

 

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)
00
0.0013888890.108333333
0.0027777780.10625
0.0041666670.104166667
0.0055555560.10625
0.0069444440.104166667
0.0083333330.104166667
0.0097222220.104166667
0.0111111110.104166667
0.01250.104166667
0.0138888890.104166667
0.0152777780.104166667
0.0166666670.104166667
0.0180555560.104166667
0.0194444440.104166667
0.0208333330.104166667
0.0222222220.104166667
0.0236111110.104166667
0.0250.104166667
0.0263888890.104166667
0.0277777780.104166667
0.0291666670.104166667
0.0305555560.104166667
0.0319444440.104166667
0.0333333330.104166667
0.0347222220.104166667
0.0361111110.104166667
0.03750.104166667
0.0388888890.104166667
0.0402777780.104166667
0.0416666670.104166667
0.0430555550.104166667
0.0444444450.104166667
0.0458333330.104166667
0.0472222220.104166667
0.0486111110.104166667
0.050.104166667
0.0513888890.10625
0.0527777780.104166667
0.0541666670.104166667
0.0555555560.104166667
0.0569444440.104166667
0.0583333330.104166667
0.0597222220.104166667
0.0611111110.104166667
0.06250.104166667
0.0638888890.104166667
0.0652777780.104166667
0.0666666670.104166667
0.0680555560.104166667
0.0694444440.104166667
0.0708333330.10625
0.0722222220.104166667
0.0736111110.104166667
0.0750.104166667
0.0763888890.104166667
0.0777777780.104166667
0.0791666670.104166667
0.0805555560.104166667
0.0819444440.104166667
0.0833333330.104166667
0.0847222220.104166667
0.0861111110.104166667
0.08750.10625
0.0888888890.10625
0.0902777780.104166667
0.0916666670.104166667
0.0930555560.10625
0.0944444450.104166667
0.0958333330.104166667
0.0972222220.104166667
0.0986111110.104166667
0.10.104166667
0.1013888890.10625
0.1027777780.104166667
0.1041666670.104166667
0.1055555560.104166667
0.1069444440.104166667
0.1083333330.104166667
0.1097222220.104166667
0.1111111110.104166667
0.11250.104166667
0.1138888890.104166667
0.1152777780.104166667
0.1166666670.104166667
0.1180555560.104166667
0.1194444440.104166667
0.1208333330.104166667
0.1222222220.104166667
0.1236111110.104166667
0.1250.104166667
0.1263888890.104166667
0.1277777780.104166667
0.1291666670.104166667
0.1305555560.104166667
0.1319444440.104166667
0.1333333330.104166667
0.1347222220.104166667
0.1361111110.104166667
1 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:

1.PNGFull 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

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

View solution in original post

7 REPLIES 7
v-shex-msft
Community Support
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(
        #"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

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

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:

Picture2.png

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:

Picture2.png

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:

1.PNGFull 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

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

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.

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.