Discrete time Integral of a value in Power Query

Question

I have an Excel table named CA-27 loaded from an Power Query script.

CA-27 contains a decimal type column named Q, representing a flow rate. It also contains a time type column named Time with YYYY-MM-DD hh:mm:ss data.

I need to run an additional step in Power Query to add a column named V with cumulative volume values. I need a formula to calculate a discrete integral of Q over Time to create column V. The formula needs to implement the trapezoidal method for discrete integration.

I know how to write formulas directly in excel cells to implement the discrete integration but I want to create the cumulative volume within Power Query, so I can process additional data using the same Power Query steps.

NOTE: Assume the last step previously used to query CA-27 is called previousStep.

I first tried the following M-language syntax to create column V within Power Query:

List.Accumulate(#"previousStep"[Q], 0,(state, current) => state + current Duration.TotalSeconds([#"CA-27.Time"] - List.First(#"previousStep"[#"CA-27.Time"])))

I expected to get the cumulative volume but I got the following error message: "Expression.Error: There is an unknown identifier".

Then I tried this alternative M-language sytnax:

let Source = previousStep, Integrated = List.Accumulate(Source[Q],0,(state, current) => state + current * Duration.TotalSeconds([CA-27.Time] - List.First(Source[CA-27.Time])) ) in Integrated

This alternatuve syntax got the following error message: "Expression.SyntaxError: Token Comma expected"

Trapezoid rule for discrete integrals:

The first data row, index=(k-1), has no defined Delta_T and V=0. For the second row onward, range(k) = [2:N], calculations are defined as:

Delta_T(k) = [Time(k) - Time(k-1)]; \\ (unit is seconds)

V(k) = 0.5 * [Q(k) + Q(k-1)] *  Delta_T(k); \\ (unit is cubic meters)

I've edited the M language code per @horseyride's suggestions as follows:

    = let Source = #"Renamed Time",
//offset by 1 row
    MinusOne = #table({"Column1"}, {{null}}) & Table.Skip(Table.DemoteHeaders(Table.RemoveLastN(#"Renamed Time",1)),1),
    custom1 = Table.ToColumns(#"Renamed Time") & Table.ToColumns(MinusOne ),
    custom2 = Table.FromColumns(custom1,Table.ColumnNames(#"Renamed Time")&List.Transform(Table.ColumnNames(#"Renamed Time"), each _&"prior")),
#"Added Custom" = Table.AddColumn(custom2, "Custom", each Duration.TotalSeconds([Time]-[Timeprior])*.5*([Qg_dh]+[Qg_dhprior])),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "IndexDT", 0, 1, Int64.Type),
#"Added Cum Total" = Table.AddColumn(#"Added Index", "Vg_dh", each List.Sum(List.FirstN(#"Added Index"[Custom],[IndexDT]+1))),
#"Removed Columns" = Table.RemoveColumns(#"Added Cum Total",{"Timeprior", "Qg_dhprior", "Custom", "IndexDT"})
in #"Removed Columns"

However, processing per this additional query step following the Rename Time step appears to result in an infinite loop, or at least an unusually long processing time for a simple cumulative result.

After waiting several minutes I got this error message:

Expression.Error: Evaluation ran out of memory and can't continue.

Answer 1

You need to post sample data as numbers, not images

That said, try

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type datetime}}),
//offset by 1 row
    MinusOne = #table({"Column1"}, {{null}}) & Table.Skip(Table.DemoteHeaders(Table.RemoveLastN(#"Changed Type",1)),1),
    custom1 = Table.ToColumns(#"Changed Type") & Table.ToColumns(MinusOne ),
    custom2 = Table.FromColumns(custom1,Table.ColumnNames(#"Changed Type")&List.Transform(Table.ColumnNames(#"Changed Type"), each _&"prior")),
#"Added Custom" = Table.AddColumn(custom2, "Custom", each Duration.TotalSeconds([Time]-[Timeprior])*.5*([Q]+[Qprior])),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1, Int64.Type),
#"Added Cum Total" = Table.AddColumn(#"Added Index", "V", each List.Sum(List.FirstN(#"Added Index"[Custom],[Index]+1))),
#"Removed Columns" = Table.RemoveColumns(#"Added Cum Total",{"Timeprior", "Qprior", "Custom", "Index"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Time", type datetime}})
in  #"Changed Type2"