Power Query - Dynamically Calculate Rows Weighted Average

[Al_Bundy]

With datatable that is essentially similar to below:

Is there a way to dynamically calculate a rolling weighted average row by row so that when there is a negative value in 'Volume' column it then uses the current inventory value (or previous rows wacog for factor value) to adjust the inventory rather than price so that it would look like below?

For 'RunningNotionalTotal' instead of using the 'Price' column when volume is negative use the previous rows calculated WACOG.  This can be done in standard in excel since the rows can be directly referenced with IF statements but using M or DAX I am getting circular references.

[Al_Bundy]

@lbendlin Thanks for the response.  I am going to put something together that will make it clearer what I'm trying to achieve.  

Problem I am having with the Index solution is the number of rows in my datatable - anywhere from ~12-15k rows.  Surprised its having issues with that many rows.  Looking into Buffer and other solutions to get the query to return faster.

[jbwtp · Accepted solution]

Hi @Al_Bundy,

Could you please try something like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY47EsAgCAXvQq0RUOyTazDe/xp+CsYJMcWj2WFnVYESUmJkhgD3GOE80IICfyFeKL9Q3N7K2ShnVH+Me+NzbpxIxrJLnCQudIlrNGNxjYbENZqyQmsd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Category = _t, Volume = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Category", type text}, {"Volume", Int64.Type}, {"Price", type number}}),
    fAddColumns = (r, e) => 
        [
            RunningTotalVolume  = Record.FieldOrDefault(e, "RunningTotalVolume", 0) + r[Volume],
            Value = (if r[Volume] < 0 then Record.FieldOrDefault(e, "WACOG", 0) else r[Price]) * r[Volume],
            RunningTotalNotional  = Record.FieldOrDefault(e, "RunningTotalNotional", 0) +  Value,
            WACOG = RunningTotalNotional / RunningTotalVolume
        ]
    ,
    #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Category"}, {{"Count", each Table.FromRecords(List.Skip(List.Accumulate(Table.ToRecords(_), {[]}, (a, n)=> a & { n & fAddColumns(n, List.Last(a))} )))}})[Count])
in
    #"Grouped Rows"

Kind regards,

John

[Al_Bundy]

Brilliant!!!  Thank you thank you John.

@jbwtp 

Now to review what you provided so I can understand what is happening!!

[lbendlin]

it then uses the current inventory value (or previous rows wacog for factor value) to adjust the inventory rather than price 

you lost me on this one.  Can you explain again in simpler terms?

Generally in Power Query you add an index column.  That will then allow you to reference the "previous row" data points.  Using List.Accumulate you can then implement the required logic.