topic Re: Date format conversion issue in Power Query

Date format conversion issue

Anonymous — Thu, 07 Nov 2019 13:31:58 GMT

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.

Re: Date format conversion issue

Mariusz — Thu, 07 Nov 2019 13:49:08 GMT

Hi Anonymous

Can you provide a data sample?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Re: Date format conversion issue

Anonymous — Thu, 07 Nov 2019 14:37:28 GMT

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,

Re: Date format conversion issue

Anonymous — Thu, 07 Nov 2019 21:58:12 GMT

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

Re: Date format conversion issue

dax — Fri, 08 Nov 2019 06:54:59 GMT

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.