Converting UNK to 01 in Power Query

[SophRow]

Hi

I'm importing date data into Power BI that in some cases uses "UNK" when either the day or month are unknown. This causes an error message:

DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
UNK-JAN-2021

I have converted the colum to text and am trying to replace "UNK" with "01" so I can convert back to date and it will be solved - but it's not working.

Any ideas?

Best,

Soph

[SophRow]

Wonderful @Vijay_A_Verma thank you!

[Vijay_A_Verma · Accepted solution]

Issue this statement where Source needs to be replaced with your previous step and Data with your column name

Table.ReplaceValue(Source,each [Data],each Text.Replace([Data],"UNK","01"),Replacer.ReplaceValue,{"Data"})

  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("i45WCvXz1vVy9NM1MjAyVIrVgQg4BgShCri4OkMFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    Custom1 = Table.ReplaceValue(Source,each [Data],each Text.Replace([Data],"UNK","01"),Replacer.ReplaceValue,{"Data"}),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Data", type date}})
in
    #"Changed Type"