Error after change data format to date

[danextian]

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]

@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]

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"