Forum Discussion

androo235's avatar
androo235
Icon for Advocate I rankAdvocate I
3 years ago

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

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.

Need Help

4 Replies

  • androo235's avatar
    androo235
    Icon for Advocate I rankAdvocate I
    3 years ago

    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's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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.