Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Error after change data format to date

Need Help

3 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi Anonymous 

    It's possibly because your dates are being interpreted as mm/dd/yyyy instead of dd/mm/yyyy as that is your current setting. To change this behaviour, you  need to specify the culture when changing to a date data type.  Example: Table.TransformColumnTypes(Source,{{"date", type date, "en-au"}})

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      @Danwxtian

      Thanks for your prompt response.

      it doesn't work as when in Power query, I change the data type to "text" for example, some rows show a number like 45456 , but others (which show error after changing the type to Date) , show no change. they are still Dates. There is something wrong with these weird Dates! Any recommendations?

      • danextian's avatar
        danextian
        Super User
        3 years ago

        Hi Anonymous ,

         

        Instead of changing the data type, I would create a custom column first to convert the numbers to actual numbers ( as they are texts) and then convert them to actual dates, delete the original date column and rename the new column. This is a sample formula in Power Query

        try Date.From(Number.From([date]), "en-au") otherwise Date.From([date], "en-au")

        Here's a sample PQ script for your reference

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU31DcyMDJUitWJVjI2QOaZmJqYminFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Date2", each try Date.From(Number.From([date]), "en-au") otherwise Date.From([date], "en-au"), type date)
in
    #"Added Custom"