Date Column Returning null

[Peter_Yaacoub]

Hi everyone!

I am  trying to create a date column based on my YearDate column (snapshot below). If YearDate Column is Current Year -1, I want to replace it  with [Merged], otherwise replace with [Merged.1].

Unfortunately the formula I am using returns null. Your help is much appreciated.

Thanks,

Peter

[Vijay_A_Verma · Accepted solution]

Insert this statement where #"Changed Type" is your previous step

= Table.ReplaceValue(#"Changed Type",each [YearDate],each if Date.Year([YearDate])=Date.Year(DateTime.FixedLocalNow())-1 then [Merged]
else [Merged.1],Replacer.ReplaceValue,{"YearDate"})

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NtQ3MjAyVNJRcgRiJyAGc2N1UCWdgdgFm6QRUNAViN0gkkYYku5A7IFL0hOIvXBJegOxD1wyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearDate = _t, Merged = _t, Merged.1 = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YearDate", type date}, {"Merged", type text}, {"Merged.1", type text}, {"Year", Int64.Type}}),
    Custom1 = Table.ReplaceValue(#"Changed Type",each [YearDate],each if Date.Year([YearDate])=Date.Year(DateTime.FixedLocalNow())-1 then [Merged]
else [Merged.1],Replacer.ReplaceValue,{"YearDate"})
in
    Custom1