Hello @Lz-3068
I need some help to calculate 2 Columns in PQ:
Interest $ and Balance
I have the following data
Investment
Cashflow
Interest Rate (Paid for every 5 days)

Hello @Lz-3068
I need some help to calculate 2 Columns in PQ:
Interest $ and Balance
I have the following data
Investment
Cashflow
Interest Rate (Paid for every 5 days)

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
Hi @Lz-3068
First: Sorry I didn't know that. I have to read the rules
Second: Spot on! Thank you very much
(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
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

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
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
3 people are following this question.