Forum Discussion

deanbland's avatar
deanbland
Helper III
5 years ago
Solved

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

Need Help
Tips and Tricks
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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.