question

JanPedersen-0386 avatar image
0 Votes"
JanPedersen-0386 asked Lz-3068 answered

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

Hello @Lz-3068

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)

102755-balancesheet.png






power-query-not-supported
balancesheet.png (344.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered JanPedersen-0386 commented

Hi @JanPedersen-0386
(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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Lz-3068

First: Sorry I didn't know that. I have to read the rules
Second: Spot on! Thank you very much

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered JanPedersen-0386 commented

Hi @JanPedersen-0386

(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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Great , I will give it a try.

Thank you very much

0 Votes 0 ·
JanPedersen-0386 avatar image
0 Votes"
JanPedersen-0386 answered JanPedersen-0386 edited

Hi @Lz-3068

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

102770-price.png




price.png (64.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@JanPedersen-0386

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JanPedersen-0386 avatar image
0 Votes"
JanPedersen-0386 answered

Hi @Lz-3068


Custom function
102967-invoke-custom-function.png



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@JanPedersen-0386

As far as I know (and I must insist on the AFAIK) invoking a function in the middle of a query, without passing the required arguments/parameters to that function, isn't doable

I have no idea where you take/get your initial Purchase price, same goes for Price increase. So I implemented them a "general" Parameters...

In this sample, Query3 simulates your query at step KeepColumns and the fxPrice function has been adjusted to take a table name + New Column Name as parameters


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.