Running Total by Category with Compound Interest. List.Accumulate/Generate

[androo235]

Data and XL solution and attempts to solve with Power Query 

I have been trying to solve this problem with power query. I started by using refer to previous row methods but thought these would likely be "too expensive" when used on my production dataset (about 500k rows) judging by how slow they were even with the sample data. I have then tried to make it work with list.accumulate and/or list.generate but, without success. The sample data and my attempts with power query along with a standard (structured table) XL solution which does actually work , even with 500k rows, and shows the result I'm aiming at (screenshot below) are in the XL file linked to above. But, if I can do this in PQ that's where I'd rather have it done.

[ImkeF]

Hi @androo235 ,
this function calculates the compound interest:

( RTColumnName as text, MyTable as table, ValueColumn as text, InterestColumn as text) =>
let
    Source = MyTable,
    BuffValues = Table.Buffer( MyTable ),
    RunningTotal = 
      List.Skip(
        List.Generate ( 
        () => [ RT = 0, ValueColumn), RowIndex = 0 ],
        each  [RowIndex] <= Table.RowCount(BuffValues),
        each  [ RT = ([RT] + Record.Field(BuffValues{[RowIndex]}, ValueColumn) )  * (1 + Record.Field(BuffValues{[RowIndex]}, InterestColumn)),
                RowIndex = [RowIndex] + 1 ],
        each  [RT] 
        )
      ),
    #"Combined Table + RT" = 
      Table.FromColumns( 
        Table.ToColumns( MyTable )   
           & { Value.ReplaceType( RunningTotal, type {Int64.Type} ) } , 
        Table.ColumnNames( MyTable ) & { RTColumnName } )
in
    #"Combined Table + RT"

also check the solution with "fnCompounding" in the file attached.

[androo235]

thanks Imke. Here's the link again (a fresh one even if it looks and is the same) It's to my personal one-drive and says it's an "anyone can view" link and is set to open in a new page. I'm assuming "can view" includes being able to download and then edit it. If it isn't possible to download and edit please tell me and I'll just have to post a "can edit" link. Data and XL Solution and attempt to solve with Power Query  Thanks.

[ImkeF]

Hi @androo235 ,
it should be possible to do this fast in PQ using the right method:
Memory efficient clustered running total in Power BI – (thebiccountant.com)
I have been using this on millions of rows without any issues.
I you are having trouble applying it to your case, please re-open the file you had shared.

[androo235]

Seems no-one has  an answer to this.