Conditional replacing of dates

[pieterjb]

Hello,

I am trying to replace dates in column "BASELINE Start Date" with a date of 1 Jan 2000 based on if the value in column "RAG Number" = 4. See querry below. It is not working somehow...

Any help much appreciated!

= Table.ReplaceValue(#"Renamed Columns2",each[BASELINE Start Date], each if [RAG Number] = "4" then [BASELINE Start Date] = #date(2000, 1, 1) else [BASELINE Start Date], Replacer.ReplaceValue,{"BASELINE Start Date"})

[BA_Pete]

Hi @pieterjb ,

Try removing the second explicit reference to your column like this. You've laready identified the column to affect in the second argument, so you shouldn't need to declare it again:

= Table.ReplaceValue(
    #"Renamed Columns2",
    each [BASELINE Start Date],
    each if [RAG Number] = "4" then #date(2000, 1, 1) else [BASELINE Start Date],         
    Replacer.ReplaceValue,{"BASELINE Start Date"}
)

If that doesn't work, try changing the Replacer type:

Replacer.ReplaceValue >> Replacer.ReplaceText

Pete

[pieterjb]

Thanks for the feedback. I have tried, but unfortunatly no luck.

[BA_Pete · Accepted solution]

Perhaps your [RAG Number] column is number type, not text type?

Try changing this:

each if [RAG Number] = "4" >> each if [RAG Number] = 4   // No quotes for number types

Pete

[pieterjb]

Hi BA_Pete,

Great! This indeed solved the problem. Many thanks!