Changing the individual values in a column based on conditions in Power Query

H

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:
AmountBilledPaymentDateOfPaymentAmountRemainig
1006024 Apr 201540
2000null200

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:
AmountBilledPaymentDateOfPaymentAmountRemainig
1000null100
2000null200

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.
 
Hello Herbds7,
Thanks for looking at the problem and sharing the file. There were some minor issues with the model when opening it in my 2013 Excel (reported different versions of PowerPivot add-in and needed "upgrading" your file, whatever that did, the slicer doesn't work as intended) BUT, at least I was able to deconstruct your take on the problem in PP, so it was helpful anyway... I would still like to see a PowerQuery solution however. The problem I face is a bit more complex and my post is just the bottom of it. A PowerPivot solution is possible but, it would require adding like five more calclated columns and I wanted to avoid that because of the amount of the data. But I do appreciate your solution, thank you very much!
 
Here is the 2010 file converted to 2013 Pro Plus.
With End-of-Month Slicer.
http://www.mediafire.com/view/b1c9y8giigdzwla/04_24_15b.xlsx
Whereas:
Your file is more complex than you let on;
The devil is in the details;
You hesitate to share that file;
Your attempted Power Query file is merely inelegant;
A conversion of our files would result in a completely different M program.
It is hereby resolved to do nothing.
 
Is this what you want?

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    setTypeDate = Table.TransformColumnTypes(Source,{{"DateOfPayment", type date}}),
    nullDates = Table.ReplaceValue(setTypeDate,null,#date(1900, 1, 1),Replacer.ReplaceValue,{"DateOfPayment"}),
    newAmount = Table.AddColumn(nullDates, "NewAmountRemaining", each if [DateOfPayment] > #date(2015,3,31) then
        [AmountRemainig] + [Payment]
    else
        [AmountRemainig]),
    deleteOldAmount = Table.RemoveColumns(newAmount,{"AmountRemainig"}),
    renameNewAmount = Table.RenameColumns(deleteOldAmount,{{"NewAmountRemaining", "AmountRemaining"}}),
    resetNullValues = Table.ReplaceValue(renameNewAmount,#date(1900, 1, 1),null,Replacer.ReplaceValue,{"DateOfPayment"})
in 
    resetNullValues
 
Hi Bob!

Thank You for Your example, that's pretty much exactly the logic I'm using now to come around the problem, I managed to avoid parts replacing dates with 1.1.1900, see excerpt of my script with all three columns adjusted:
Code: 
DateOfReport = #date(2015,3,31),

// check payments after DateOfReport
#"Prepare Columns" = Table.RenameColumns(#"Rearranged Table",{{"AmountRemaing", "old_AmountRemaing"}, {"Payment", "old_Payment"}, {"[COLOR=#333333]DateOfPayment[/COLOR]", "old_[COLOR=#333333]DateOfPayment[/COLOR]"}}),
#"Step 1" = Table.AddColumn(#"Prepare Columns", "AmountRemaing", each[INDENT]if [old_[COLOR=#333333]DateOfPayment[/COLOR]] <> null and [old_[COLOR=#333333]DateOfPayment[/COLOR]] > DateOfReport[/INDENT]
[INDENT]then [old_AmountRemaing] + [old_Payment]
else [old_AmountRemaing]),[/INDENT]
#"Step 2" = Table.AddColumn(#"Step 1", "Payment", each[INDENT]if [old_[COLOR=#333333]DateOfPayment[/COLOR]] <> null and [old_[COLOR=#333333]DateOfPayment[/COLOR]] > DateOfReport
then 0
else [old_Payment]),[/INDENT]
#"Step 3" = Table.AddColumn(#"Step 2", "[COLOR=#333333]DateOfPayment[/COLOR]", each[INDENT]if [old_[COLOR=#333333]DateOfPayment[/COLOR]] <> null and [old_[COLOR=#333333]DateOfPayment[/COLOR]] > DateOfReport[/INDENT]
[INDENT]then null[/INDENT]
[INDENT]else [old_[COLOR=#333333]DateOfPayment[/COLOR]]),[/INDENT]
#"Clean Up" = Table.RemoveColumns(#"Step 3",{"old_AmountRemaing", "old_Payment", "old_[COLOR=#333333]DateOfPayment[/COLOR]"})
However, I consider this solution not only "inelegant", how Herbds7 put it :) but perhaps even memory inefficient, because it's necessary to create whole new columns for possibly vast number of rows. So I was wondering whether it can be done on those columns (or rows, depends) already in existence, where all three steps could be done in one pass without having to initiate new data. I've been considering function Table.TransformRows but I admit I'm unable to write it in a way suggested in my original post. Can it be done in PowerQuery?
 
Hi Herbds7!
Thanks for the updated file, works as advertised ;-) You're right with my hesitation, I'm getting familiar with Power Query add-in on some actual real data I have at my disposal, hence I can't share my whole data files. That's why I created those simple substitude tables in my original post. Anyway, I am planning to look into Power Pivot as well, after I've spent more time with Power Query... I like how it works in Your file. But still, being able to write it in PQ would be nice!
 
Hank Johnson said:
Hi Bob!
However, I consider this solution not only "inelegant", how Herbds7 put it :) but perhaps even memory inefficient, because it's necessary to create whole new columns for possibly vast number of rows. So I was wondering whether it can be done on those columns (or rows, depends) already in existence, where all three steps could be done in one pass without having to initiate new data. I've been considering function Table.TransformRows but I admit I'm unable to write it in a way suggested in my original post. Can it be done in PowerQuery?
Click to expand...

I don't think it is inefficient, those are only temporary memory columns, you tidy-up at the end. And if the temporary column has mainly dates of 1/1/1900, compression will be high. I think the If might be inefficient though, especially 3 of them, I would think that the one Replace is more efficient.
 
Last edited:
You must log in or register to reply here.
Back
Top