Replace date if less than....

[SammiF1244]

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"})

[ronrsnfld · Accepted solution]

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 · Accepted solution]

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"

[ronrsnfld · Accepted solution]

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"