Date field formatting

Hi Anonymous 

It is an odd one, no idea why this is happening but you can fix it by adding an extra "transform" step as per below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzTUNzIwNFOK1YlWMjEyNwKyYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    transform = Table.TransformColumns(Source,{{"Date", each try Number.FromText( _ ) otherwise _, type date }}),
    #"Changed Type" = Table.TransformColumnTypes(transform,{{"Date", type date}})
in
    #"Changed Type"

Or optional see the attached for the ref.

Hi Anonymous , 

It seems not to  be achieved directly, I think you could try below M code to see whether it work or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRNzTUNzIwNFOK1QFyjaBcCzDX2EDf0AjEtQRzTYzMjcyQWbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Date],"/") then 1 else 2),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each [Date], type list}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", type any}, {"2", type any}}),
    #"Expanded 1" = Table.ExpandListColumn(#"Changed Type", "1"),
    #"Expanded 2" = Table.ExpandListColumn(#"Expanded 1", "2"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded 2", {{"1", type date}}, "aa-DJ"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"2", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"2", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Duplicates", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.