Forum Discussion

SammiF1244's avatar
SammiF1244
Helper I
2 years ago
Solved

Replace date if less than....

I can't figure out what I am doing wrong even looking at examples.

I've tried this:

= Table.ReplaceValue(each #"Removed Columns1",each if <#date(2015, 10, 1),then #date(2015, 10, 1), else [CODE_EFFECTIVE_DATE],Replacer.ReplaceValue,{"CODE_EFFECTIVE_DATE"})

and this: 

= Table.ReplaceValue(#"CODE_EFFECTIVE_DATE",each if <#date(2015, 10, 1),then #date(2015, 10, 1), else [CODE_EFFECTIVE_DATE],Replacer.ReplaceValue,{"CODE_EFFECTIVE_DATE"})

and this:

= Table.ReplaceValue(#"CODE_EFFECTIVE_DATE",< #date(2015, 10, 1),#date(2015, 10, 1),Replacer.ReplaceValue,{"CODE_EFFECTIVE_DATE"})

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Here's one way:

     

     

     

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE_EFFECTIVE_DATE", type date}}),
    #"Replace Date" = Table.ReplaceValue(
        #"Changed Type",
        each [CODE_EFFECTIVE_DATE],
        #date(2015,10,1),
        (x,y,z) => if x < z then z else y,
        {"CODE_EFFECTIVE_DATE"}
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replace Date",{{"CODE_EFFECTIVE_DATE", type date}})
in
    #"Changed Type1"

     

     

    and here is another with slightly longer code:

     

       #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE_EFFECTIVE_DATE", type date}}),
    #"Replace Date" = Table.ReplaceValue(
        #"Changed Type",
        each [CODE_EFFECTIVE_DATE],
        each if [CODE_EFFECTIVE_DATE] < #date(2015,10,1) then #date(2015,10,1) else [CODE_EFFECTIVE_DATE],
        Replacer.ReplaceValue,
        {"CODE_EFFECTIVE_DATE"}
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replace Date",{{"CODE_EFFECTIVE_DATE", type date}})
in
    #"Changed Type1"

     

     

     

     

  • SammiF1244's avatar
    SammiF1244
    2 years ago

    Thank you. I didn't understand the first one, so I used the second, but I am getting change type already defined, so I left out the first line 

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE_EFFECTIVE_DATE", type date}}),

    and it worked 🙂

     

    #"Replace Date" = Table.ReplaceValue(
    #"Changed Type",
    each [CODE_EFFECTIVE_DATE],
    each if [CODE_EFFECTIVE_DATE] < #date(2015,10,1) then #date(2015,10,1) else [CODE_EFFECTIVE_DATE],
    Replacer.ReplaceValue,
    {"CODE_EFFECTIVE_DATE"}
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replace Date",{{"CODE_EFFECTIVE_DATE", type date}})
    in
    #"Changed Type1"

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Here's one way:

     

     

     

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE_EFFECTIVE_DATE", type date}}),
    #"Replace Date" = Table.ReplaceValue(
        #"Changed Type",
        each [CODE_EFFECTIVE_DATE],
        #date(2015,10,1),
        (x,y,z) => if x < z then z else y,
        {"CODE_EFFECTIVE_DATE"}
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replace Date",{{"CODE_EFFECTIVE_DATE", type date}})
in
    #"Changed Type1"

     

     

    and here is another with slightly longer code:

     

       #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE_EFFECTIVE_DATE", type date}}),
    #"Replace Date" = Table.ReplaceValue(
        #"Changed Type",
        each [CODE_EFFECTIVE_DATE],
        each if [CODE_EFFECTIVE_DATE] < #date(2015,10,1) then #date(2015,10,1) else [CODE_EFFECTIVE_DATE],
        Replacer.ReplaceValue,
        {"CODE_EFFECTIVE_DATE"}
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replace Date",{{"CODE_EFFECTIVE_DATE", type date}})
in
    #"Changed Type1"

     

     

     

     

  • SammiF1244's avatar
    SammiF1244
    Helper I
    2 years ago

    Thank you. I didn't understand the first one, so I used the second, but I am getting change type already defined, so I left out the first line 

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE_EFFECTIVE_DATE", type date}}),

    and it worked 🙂

     

    #"Replace Date" = Table.ReplaceValue(
    #"Changed Type",
    each [CODE_EFFECTIVE_DATE],
    each if [CODE_EFFECTIVE_DATE] < #date(2015,10,1) then #date(2015,10,1) else [CODE_EFFECTIVE_DATE],
    Replacer.ReplaceValue,
    {"CODE_EFFECTIVE_DATE"}
    ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replace Date",{{"CODE_EFFECTIVE_DATE", type date}})
    in
    #"Changed Type1"