Hank Johnson
New member
- Joined
- Apr 24, 2015
- Messages
- 4
- Reaction score
- 0
- Points
- 0
Hi guys,
say I have a data in this simplified table:
I need to be able to make reports from the data at a specific date, say end of March 2015, so I need to change the first row to the state it was at 31 Mar 2015, i.e. change it to look like this:
How do I do this elegantly in Power Query? I have thousands of rows, say the table name is "TPayments", so I would like to do something like with the table:
for each row of "TPayments" do
if [DateOfPayment] > SomeArbitraryDate then
{
[AmountRemaining] = [AmountRemaning] + [Payment],
[Payment] = 0,
[DateOfPayment] = null
}
I've been searching the functions library for PowerQuery and still don't know how to do it! Anyone? Thank You very much!
Hank.
say I have a data in this simplified table:
AmountBilled | Payment | DateOfPayment | AmountRemainig |
100 | 60 | 24 Apr 2015 | 40 |
200 | 0 | null | 200 |
I need to be able to make reports from the data at a specific date, say end of March 2015, so I need to change the first row to the state it was at 31 Mar 2015, i.e. change it to look like this:
AmountBilled | Payment | DateOfPayment | AmountRemainig |
100 | 0 | null | 100 |
200 | 0 | null | 200 |
How do I do this elegantly in Power Query? I have thousands of rows, say the table name is "TPayments", so I would like to do something like with the table:
for each row of "TPayments" do
if [DateOfPayment] > SomeArbitraryDate then
{
[AmountRemaining] = [AmountRemaning] + [Payment],
[Payment] = 0,
[DateOfPayment] = null
}
I've been searching the functions library for PowerQuery and still don't know how to do it! Anyone? Thank You very much!
Hank.