Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
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"