Forum Discussion
Can't change date data type
Hi,
My months are written in text format (January), and when trying to change the format of the column to 'Date' PBI throws up this error, "DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
January"
Could someone advise on how to correct this please?
Thanks
Hi deanbland
I'm assuming you just have the month name in a column? If so you can add a Custom Column and use Date.FromText to create a date.
Here's a sample PBIX file.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlSK1YlWcktNKoJzfBOLkjPALMeCoswcqBhEzqs0LxXKyIGIOJamlxaXgJnBqQUlqblJqUVgnn9yST6M7ZdfhpBwSU2GcmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Months = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.FromText("1 " & [Months] & " 2020")), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}) in #"Changed Type"Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
2 Replies
We can't transfer month name to date. We can only transfer date to month name
you can create a calendar table
https://docs.microsoft.com/en-us/dax/calendar-function-dax
transfer to month name
month = FORMAT('Table'[date],"mmmm")Hi deanbland
I'm assuming you just have the month name in a column? If so you can add a Custom Column and use Date.FromText to create a date.
Here's a sample PBIX file.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMK00sqlSK1YlWcktNKoJzfBOLkjPALMeCoswcqBhEzqs0LxXKyIGIOJamlxaXgJnBqQUlqblJqUVgnn9yST6M7ZdfhpBwSU2GcmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Months = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.FromText("1 " & [Months] & " 2020")), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}) in #"Changed Type"Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
