Results 1 to 8 of 8

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,

    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.

  2. #2
    Excel 2010 with free Power Query and PowerPivot Add-In.
    Compatible with Office 2013 Pro Plus.
    This might be easier with PowerPivot.
    Share file if you get stuck.
    http://www.mediafire.com/view/hltuxg.../04_24_15.xlsx

  3. #3
    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!

  4. #4
    Here is the 2010 file converted to 2013 Pro Plus.
    With End-of-Month Slicer.
    http://www.mediafire.com/view/b1c9y8...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.

  5. #5
    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

  6. #6
    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"}, {"DateOfPayment", "old_DateOfPayment"}}),
    #"Step 1" = Table.AddColumn(#"Prepare Columns", "AmountRemaing", each
    if [old_DateOfPayment] <> null and [old_DateOfPayment] > DateOfReport
    then [old_AmountRemaing] + [old_Payment] else [old_AmountRemaing]),
    #"Step 2" = Table.AddColumn(#"Step 1", "Payment", each
    if [old_DateOfPayment] <> null and [old_DateOfPayment] > DateOfReport then 0 else [old_Payment]),
    #"Step 3" = Table.AddColumn(#"Step 2", "DateOfPayment", each
    if [old_DateOfPayment] <> null and [old_DateOfPayment] > DateOfReport
    then null
    else [old_DateOfPayment]),
    #"Clean Up" = Table.RemoveColumns(#"Step 3",{"old_AmountRemaing", "old_Payment", "old_DateOfPayment"})
    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?

  7. #7
    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!

  8. #8
    Quote Originally Posted by Hank Johnson View Post
    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?
    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 by Bob Phillips; 2015-04-28 at 11:19 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •