Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I'm working on a query and I would convert a date column type text to a date format and I have this error message :
DataFormat.Error: Sorry ... We were unable to parse the input provided to a Date value.
Details:
April-19
Do you have any tips to solve this issue?
Thank you for your answer.
Solved! Go to Solution.
Hi @Anonymous
= Table.AddColumn(Source, "Date (Date format)", each Date.FromText("01-" & Text.End([#"Date (text format)"],3) & "-" & Text.Start([#"Date (text format)"],2)), type date)
where Source needs to be replaced by a preceding step name.
Or from ribbon Add Column > Custom Column and use this formula:
Date.FromText("01-" & Text.End([#"Date (text format)"],3) & "-" & Text.Start([#"Date (text format)"],2))
Kind regards,
John
Hi Hicham,
You could try below M code to see whether it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQdSwoUorVATN9EyshTEtdr8Q8GNM3sQguWgoUjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [month = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each "20"&[month]&"-01"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
#"Changed Type"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Mariusz here a data sample:
For information, the data came from the CSV file.
the column Date (Date format) is the attending result.
regards,
Hi Hicham,
You could try below M code to see whether it work or not.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQdSwoUorVATN9EyshTEtdr8Q8GNM3sQguWgoUjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [month = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each "20"&[month]&"-01"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
#"Changed Type"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
= Table.AddColumn(Source, "Date (Date format)", each Date.FromText("01-" & Text.End([#"Date (text format)"],3) & "-" & Text.Start([#"Date (text format)"],2)), type date)
where Source needs to be replaced by a preceding step name.
Or from ribbon Add Column > Custom Column and use this formula:
Date.FromText("01-" & Text.End([#"Date (text format)"],3) & "-" & Text.Start([#"Date (text format)"],2))
Kind regards,
John
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |