Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Converting UNK to 01 in Power Query

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

Need Help
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"