Can't change date data type

[deanbland]

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

[PhilipTreacy · Accepted solution]

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.

[ryan_mayu]

@deanbland 

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")