Log In
Register
Home
Active Member
May 2, 2023
Offline
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!AtBSy5wV0o.....g?e=lqbbaQ
Kind regards,
John
See Answer
Trusted Members
February 13, 2021
Offline
Mynda has a great video on the subject, you can find it here, come back if you need further help.
Answers Post
October 5, 2010
Offline
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
1 Guest(s)
