Self referencing calculated column (previous row) to perform calculations

[Faroacces0]

Hi everyone,

I have a list of operations of buying/selling shares of different financial products and, for each of them, I am trying to compute the average buying cost of the total position at time of transaction using Power Query. Due to how the value is computed, a formula referencing the previous cell of the new column being created is necessary.

The table is formatted as the one below, even though grouping by "Product" may be a good start. I am trying to replicate column F in PowerQuery, if it is even possible.

Thank you

[j_ocean]

You can try this GUI process (thinking through in my head might not be 100% right):

1. Split off a query. Group by product with a min date.
2. Merge this on both product and date columns into a copy of the original table, then expand date. Most will be null.
3. Sort by product, then by the full date column, and add an index.
4. Add a column doing your row 2 calc only where the merged date is not null
5. Fill the result down. You now have your baseline on each row.
6. Split off another query and -1 on the index, merge it back in matching on the index. Expand E and F. This puts the prior row on each row.
7. You now have all the data lined up on each row to make a new column using your row 3 etc formulas with a bit of logic (again on date <> null) to detemrine if you're going to carry over the baseline from step 5 or apply the other formula.

[AlienSx]

Hello, @Faroacces0 

let
    Source = your_table,
    f = (tbl as table) =>
        [rows = Table.ToRecords(tbl),
        gen = List.Generate(
            () => [i = 0, r = rows{0}, nc = r & [new_column = r[Total Cost] / r[Shares]]],
            (x) => rows{x[i]}? <> null,
            (x) => 
                [i = x[i] + 1, 
                r = rows{i}, 
                nc = r & [new_column = 
                    if r[Total Cost] > 0 
                    then (r[Total Cost] + x[r][#"RT-Shares"] * x[nc][new_column]) / r[#"RT-Shares"]
                    else x[nc][new_column]]],
            (x) => x[nc]
        )][gen],
    g = Table.Group(Source, {"Product"}, {{"new", each f(Table.Sort(_, "date"))}}),
    z = Table.FromRecords(List.Combine(g[new]))
in
    z