Power Query - Balance Sheet (Row Context , Previous Row)

Question

Hello @Lz._

I need some help to calculate 2 Columns in PQ:

Interest $ and Balance

I have the following data

1. Investment
2. Cashflow
3. Interest Rate (Paid for every 5 days)

Answer 1

Hi @Djpejsen
(Kind reminder: you're not supposed to write to me until I responded once)

Thanks for the quality of the sample & the calculations. The approach is similar to your previous case but less complex :)). Assuming data in Table1:

let  
    Source = Table1,  
    AddedIndex = Table.AddIndexColumn(Source, "ID", 0, 1),  
    GenInterestAndBalance = List.Generate(()=>  
        [i = 0,   
         Interest = AddedIndex[Cashflow]{i} * AddedIndex[#"Interest %"]{i},  
         Balance = AddedIndex[Cashflow]{i} + (1*AddedIndex[#"Interest %"]{i})  
        ],  
        each [i] < Table.RowCount(AddedIndex),  
        each  
            [  
                i = [i] + 1,  
                Interest = AddedIndex[#"Interest %"]{i} * [Balance],  
                Balance = ([Balance]*AddedIndex[#"Interest %"]{i})  
                          + [Balance] + AddedIndex[Investment]{i}  
            ],  
        each [ [Interest], [Balance] ]  
      
    ),  
    ToTable = Table.FromColumns(  
        Table.ToColumns(AddedIndex) &  
        Table.ToColumns(Table.FromRecords(GenInterestAndBalance,  
            type table [Interest=number, Balance=number])  
        ),  
        Table.ColumnNames(AddedIndex) & {"Interest $", "Balance"}  
     ),  
    RemovedIndex = Table.RemoveColumns(ToTable,{"ID"})  
in  
    RemovedIndex  

If you want to turn it as a function let me know. It probably makes sense to buffer the AddedIndex table

Corresponding sample avail. here
Cheers

Answer 2

Hi @Djpejsen

(no worrrie at all as I don't mind. However, as contributor to the forum I should remind...)

Re. this kind of running total(s) be aware this isn't something PQ does easily, I mean:

1. Requires an Index and adding one has a cost, especially on very large tables
2. Referencing a table row by its row/index number isn't ideal from an efficiency perspective

ImkeF found a technique to ref. the previous/next row more efficiently. Unfortunately this can't be used on this case :(

An alternative to referencing a table row by its row/index number would be to do something like below. I don't think (but not sure) this will make a difference on a small table. Not sure either on a large table but to keep in mind and to test in case you hit performance issues:

GenInterestAndBalance = List.Generate(()=>  
    [i = 0,  
        FirstRecord = Table.First(AddedIndex),  
        Interest = FirstRecord[Cashflow] * FirstRecord[#"Interest %"],  
        Balance = FirstRecord[Cashflow] + (1 * FirstRecord[#"Interest %"])  
    ],  
    each [i] < Table.RowCount(AddedIndex),  
    each  
        [  
            i = [i] + 1,  
            RequiredRecord = Table.First( Table.Skip(AddedIndex, i) ),  
            Interest = RequiredRecord[#"Interest %"] * [Balance],  
            Balance = ([Balance] * RequiredRecord[#"Interest %"])  
                        + [Balance] + RequiredRecord[Investment]  
        ],  
    each [ [Interest], [Balance] ]      
),  

Hope this helps
Cheers

Answer 3

Hi @Lz._

I am trying to learn how to create the List.Generate based on the previous function `enter code here

 (MyTable as table)=>  
    let  
         BufferedTable =MyTable,  
         Source =MyTable,  
         AddedIndex = Table.AddIndexColumn(Source, "ID", 0, 1),  
         BufferedIndex = Table.Buffer(AddedIndex),  
        GenInterestAndBalance = List.Generate(()=>  
             [i = 0,   
                
              // PurchPrices is created as an imported Parameter   
              // PriceIncrease is created as an imported Parameter   
              Balance = PurchPrice + (1 * PriceIncrease)  
             ],  
             each [i] < Table.RowCount(BufferedIndex),  
             each  
                 [  
                     i = [i] + 1,  
                       
                     Balance = [Balance] + (1 * PriceIncrease)  
                               
                 ],  
             each [ [Balance] ]  
              
         )   
         // The above step "GenInterestAndBalance" will give me a table column with a list of lists  
         // How to extract the above "list as lists" to a new column?   
      
         /*ToTable = Table.FromList(  
             Table.ToColumns(BufferedIndex) &  
             Table.ToColumns(Table.FromRecords(GenInterestAndBalance,  
                 type table [Balance=number])  
             ),  
               
             Table.ColumnNames(BufferedIndex) & {"Price"}  
               
          ),  
        ChangedDataType = Table.TransformColumnTypes(GenInterestAndBalance ,{<!-- -->{"Price", Currency.Type}}),  
        RemovedIndex = Table.RemoveColumns(ChangedData,{"ID"})*/  
          
    in  
        GenInterestAndBalance //RemovedIndex  

Answer 4

@Djpejsen

Not sure what you exactly mean (what's expected) with "How to extract the above 'list as lists' to a new column". To a new column to what???

Below code is available in this workbook. If you follow the steps with PQ Applied Steps hopefully this should answer your question. If not please open a separate thread with a demo table (few rows are enough) and what you expect once the list of prices increase has been generated

let  
    // Table for demo:  
    Source = Table.FromColumns({ {"a".."e"}, {"Hello", "Jens", "how", "are", "you?"} },  
        type table [Alpha=text, Greetings = text]  
    ),  
  
    // Imported parameters simul:  
        PurchPrice = 100,  
        PriceIncrease = 0.5,  
  
    myFunction = (MyTable as table) as list=>  
        let  
            GenPriceInc = List.Generate(()=>  
                [i = 0, PriceInc = PurchPrice + (1 * PriceIncrease)],  
                each [i] < Table.RowCount(MyTable),  
                each  
                    [  
                        i = [i]+1,  
                        PriceInc = [PriceInc] + (1 * PriceIncrease)  
                    ],  
                each [PriceInc]  
            )  
        in  
            GenPriceInc, // Returns a list of items (num. values in this case)  
    CalledMyFunction = myFunction(Source),  
  
    // CASE 1: I want to create a New table from the above  
    // list (output of CalledMyFunction)  
    ListToTable = Table.FromColumns({ CalledMyFunction },  
        type table [Price Increase=number]  
    ),  
  
    // CASE 2: I want the above list (output of CalledMyFunction)  
    // to be a new column to my Source table (steps decomposed below)  
    SourceTableAsListOfLists = Table.ToColumns(Source),  
    AppendedGenPriceIncList = SourceTableAsListOfLists & {CalledMyFunction},  
        // OR: List.Combine({ SourceTableAsListOfLists, {CalledMyFunction} })  
    AboveListOfListsAsTable = Table.FromColumns(AppendedGenPriceIncList,  
        type table [AlphaNewColumnName=text, GreetingsAsComment=text, PriceIncreas=number]  
    )  
in  
    AboveListOfListsAsTable  

Answer 5

Hi @Lz._

Custom function