Power Query Simple Recursive Custom Column Ending Value

[djburch15]

Hi everyone,

I'm having trouble creating a simple recursive formula that allows me to reference a newly created column value as the starting value in a subsequent calculation. See the excel based screenshot below — I need to create a summarized End_Value based on activity during the Month, then conditionally reference that End_Value by ID in order to use it as the Start_Value in the following Month. I've highlighted month 1 ending values and month 2 starting values in green for illustrative purposes. Any advice here would be greatly appreciated!

[dufoq3]

It works for data you provided. If you want me to add the code - provide new sample data please.

[djburch15]

@dufoq3 this appears to work perfectly when's there a single grouped category, however, I have seven separate grouped categories in practice and the above code doesn't seem to be able to reference the newly calculated Start_Value (End_Value = Adds + Removals) in future months

[dufoq3 · Accepted solution]

Hi @djburch15, check this:

• If you don't need to preserve sort order, delete steps AddedIndex, SortedRows and RemovedColumns (it will increase speed if you have bigger dataset).
• If you want to calculate also Start_Value, you can create new custom colum: End_Value minus Adds minus Removals

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7RCcAwCEXRXfxOQJ9Ih5Hsv0aN2iIk/hy8iTuJCC3KyxwTFmOL0VlOABqRKHds40RVbTT+17cUVvZux3myCR7Nltz8pIIt/Y9Z01mz+cMWS1EbtRZBPRR0Xg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Month = _t, Start_Value = _t, Adds = _t, Removals = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Month", Int64.Type}, {"Start_Value", type number}, {"Adds", type number}, {"Removals", type number}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    fn_EndValue = 
        (myTable as table)=>
        [ a = Table.Buffer(Table.SelectColumns(myTable, {"Start_Value", "Adds", "Removals"})),
        lg = 
            List.Generate(
                    ()=> [ x=0, y = List.Sum(Record.ToList(a{x})) ],
                    each [x] < Table.RowCount(a),
                    each [ x = [x]+1, y = [y] + List.Sum(Record.ToList(a{x})) ],
                    each [y]
            ),
        b = Table.FromColumns(Table.ToColumns(myTable) & {lg}, Value.Type(myTable & #table(type table[End_Value=number],{}) ) )
        ][b],
    GroupedRows = Table.Group(AddedIndex, {"ID"}, {{"All", each fn_EndValue(_), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    SortedRows = Table.Sort(CombinedAll,{{"Index", Order.Ascending}}),
    RemovedColumns = Table.RemoveColumns(SortedRows,{"Index"})
in
    RemovedColumns