DateError in Power Query when i close & Load

Hi thorrrr,

If your date records are stored with "dd/mm/yyyy" format, you can use Date.From() function to format them to default format, then convert these columns to date type.

For example:

Table.

Try to direct convert the field to date:

Add custom column to format the filed.

Formula:

AddCustom =Table.AddColumn(#"Changed Type", "Formted Column1", each Date.From([Column1],"uk"))

Change type:

Choose columns:

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQNzTUNzIwNFPSUQKyoOxYHaCUIZKUqb4FkowRsiYjfSNjqFwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}}),
    AddCustom =Table.AddColumn(#"Changed Type", "Formted Column1", each Date.From([Column1],"uk")),
    #"Format Date Fileds" = Table.TransformColumnTypes(AddCustom,{{"Formted Column1", type date}}),
    #"Choose columns" = Table.SelectColumns(#"Format Date Fileds",{"Formted Column1","Column2"})
in
    #"Choose columns"

In addition, you can also solve this issue by modify the "Locale" option, but if your data source contain multiple format of date, I'd like to suggest you use above function.

Regards,

Xiaoxin Sheng