Forum Discussion
Simple recursive calculation - Power Query
Hello,
I'm trying to build the following calculation in Power Query:
| d | t | p | t' = t x s (d-1) | s = s (d-1) + p + t' |
| 0 | - | 3.600.000 | ||
| 1 | 1,20% | (200.000) | 43.200 | 3.443.200 |
| 2 | 0,60% | (300.000) | 20.659 | 3.163.859 |
| 3 | 0,40% | (700.000) | 12.655 | 2.476.515 |
| 4 | 0,50% | (650.000) | 12.383 | 1.838.897 |
Values in black are inputs and values in red are the results I want to get.
In excel is very simple, basically I want to get the previous row (s{d-1}), calculate the correction (t' = s{d-1} x t) and then calculate the next s (s{d-1} + p + t'), and repeat. But, I'm having problems building this logic into M language in Power Query. Someone could help, please?
Greg_DecklerHello Greg
Thank you for your reply and for sharing those articles, it help me a lot!
Still struggle a little to refer the values I wanted to use in calculations, but I manage to solve this problem creating a new column of lists instead (if you know a better way to do it, I would love to hear it).
Here is the code I used, in case someone else want to know:let fxBalance = (InitialValue,Payments,InterestRate,Counter,Index) => let Correction = if InterestRate{Counter-1} = null then 0 else InitialValue*InterestRate{Counter-1}, FinalBalance = if InitialValue - Payments{Counter-1} + Correction < 0 then 0 else InitialValue - Payments{Counter-1} + Correction, Return = if FinalBalance < 0 or Counter >= Index then FinalBalance else @fxBalance(FinalBalance,Payments,InterestRate,Counter+1,Index) in Return in fxBalance
Using the function:Table.AddColumn(#"Personalização Adicionada6", "Personalizar.1", each fxBalance([Initial Value],[Payments List],[Interest Rate List],1,[Index]))
The results:
ty
2 Replies
- Greg_Deckler
Community Champion
brunoiadocicco Last time I was messing around with recursion in Power Query, it had to do with fractals. Attaching PBIX below. Here is a link to the articles. The articles have links to download additional PBIX file examples. Should be something in there to help your particular situation, I think I did a different solution for recursion in each article.
Greg_DecklerHello Greg
Thank you for your reply and for sharing those articles, it help me a lot!
Still struggle a little to refer the values I wanted to use in calculations, but I manage to solve this problem creating a new column of lists instead (if you know a better way to do it, I would love to hear it).
Here is the code I used, in case someone else want to know:let fxBalance = (InitialValue,Payments,InterestRate,Counter,Index) => let Correction = if InterestRate{Counter-1} = null then 0 else InitialValue*InterestRate{Counter-1}, FinalBalance = if InitialValue - Payments{Counter-1} + Correction < 0 then 0 else InitialValue - Payments{Counter-1} + Correction, Return = if FinalBalance < 0 or Counter >= Index then FinalBalance else @fxBalance(FinalBalance,Payments,InterestRate,Counter+1,Index) in Return in fxBalance
Using the function:Table.AddColumn(#"Personalização Adicionada6", "Personalizar.1", each fxBalance([Initial Value],[Payments List],[Interest Rate List],1,[Index]))
The results:
ty
