Hello,
I hope you are all well.
I'm just an average excel user. I need help to create a power query for running balance/total.
Basically in an ordinary excel formula, I can compute for running balance/ total by:
1st row -> Payout - Cash in (B2-A2)
2nd row -> Payout - Cash in + difference of previous row (B3-A3) + C2.
3rd row -> Payout - Cash in + difference of previous row (B4-A4) + C3.
So on and so forth...
| Cash in | Payout | Running Balance |
| 0.25 | 0 | -0.25 |
| 0.75 | 0 | -1 |
| 1.5 | 0.5 | -2 |
| 1.25 | 0.5 | -2.75 |
| 1 | 0 | -3.75 |
| 0.25 | 0 | -4 |
| 0.75 | 0.3 | -4.45 |
| 0.5 | 0 | -4.95 |
| 2.25 | 0 | -7.2 |
| 2.25 | 0 | -9.45 |
| 0.75 | 2.5 | -7.7 |
| 1 | 0 | -8.7 |
Can anyone help me please apply this in power query? I know I could just use the basic formula, but the reason why I want it in power query is because, I also have an updating data that I have also made some formatting in power query and I wanted to integrate this with my existing.
Appreciate if you can be detailed in the explanation that you will be providing. Thank you so much in advance.
Here is the link to my excel file: https://1drv.ms/x/s!AtBSy5wV0oBqgvIdcz8lLtx9V2wo3g?e=lqbbaQ
Kind regards,
John
Mynda has a great video on the subject, you can find it here, come back if you need further help.
Hi John,
This isn't your typical Running Total so the code I wrote for Power Query Running Totals would not work without modification.
So, I've written a different query that uses List.Sum and List.FirstN to SUM the differences between each row. This requires creating a temp column to calculate these differences, then you just sum them.
See attached file.
Regards
Phil
Hi Team,
Thank you so much for your help I was now able to make it work. Appreciate it so much.
More power!
Kind regards,
John
