none
PowerQuery: Replace Value with Conditional if and the actual Date RRS feed

  • Question

  • Hello,

    i have the following problem. I want to update the values of a column with following condition.

    #"blabla" = Table.ReplaceValue(#"Table","DateValue1_month","DateValue2_month",Replacer.ReplaceValue,{"column"}),

    but i have to check first

    if

    DateValue1_month < ActualMonth

    then

    DateValue1_month = ActualMonth

    else

    keep DateValue1_month

    fi

    Can some help me with this

    Tuesday, April 14, 2015 2:45 PM

Answers

  • I'm a little confused by the question because I can't tell whether DateValue1_month and DateValue2_month represent columns or values. If values, then I'm not sure why they would be quoted in the call to Table.ReplaceValue. Can you flesh out the problem a little with more-concrete examples?

    What I'm guessing is that you want to do something like "if the value in the column is less than ActualMonth then replace it with ActualMonth; otherwise, keep it unchanged." While this kind of operation can be done with Table.ReplaceValue, It's not straightforward. The problem is that you need to split the logic between a custom replacer function and several literals. If this is what you want to do, I would use Table.TransformColumns and say

    Transformed = Table.TransformColumns(table, {"column", each if _ < ActualMonth then ActualMonth else _})

    • Marked as answer by Piedy Friday, April 17, 2015 6:00 AM
    Thursday, April 16, 2015 10:30 AM
  • ? How about *please* :-)

    #"blabla" = if DateValue1_month < ActualMonth

    then Table.ReplaceValue(#"Table","DateValue1_month","DateValue2_month",Replacer.ReplaceValue,{"column"})

    else DateValue1_month,


    Imke

    • Marked as answer by Piedy Friday, April 17, 2015 6:00 AM
    Thursday, April 16, 2015 7:36 AM
    Moderator

All replies

  • *push* :-)
    Thursday, April 16, 2015 6:29 AM
  • ? How about *please* :-)

    #"blabla" = if DateValue1_month < ActualMonth

    then Table.ReplaceValue(#"Table","DateValue1_month","DateValue2_month",Replacer.ReplaceValue,{"column"})

    else DateValue1_month,


    Imke

    • Marked as answer by Piedy Friday, April 17, 2015 6:00 AM
    Thursday, April 16, 2015 7:36 AM
    Moderator
  • I'm a little confused by the question because I can't tell whether DateValue1_month and DateValue2_month represent columns or values. If values, then I'm not sure why they would be quoted in the call to Table.ReplaceValue. Can you flesh out the problem a little with more-concrete examples?

    What I'm guessing is that you want to do something like "if the value in the column is less than ActualMonth then replace it with ActualMonth; otherwise, keep it unchanged." While this kind of operation can be done with Table.ReplaceValue, It's not straightforward. The problem is that you need to split the logic between a custom replacer function and several literals. If this is what you want to do, I would use Table.TransformColumns and say

    Transformed = Table.TransformColumns(table, {"column", each if _ < ActualMonth then ActualMonth else _})

    • Marked as answer by Piedy Friday, April 17, 2015 6:00 AM
    Thursday, April 16, 2015 10:30 AM
  • Ha that works both for me :-)

    Thank you very much @ Imke and  @ Curt

    :-)

    Friday, April 17, 2015 6:00 AM