Forum Discussion
Conditional replacing of dates
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"})
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 typesPete
4 Replies
Hi Anonymous ,
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.ReplaceTextPete
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 typesPete