I have a mix of both US and UK dates in the same column

[Butters]

I have a mix of both US and UK dates in the same column.  I need to figure out how to get them all into the US format.  When I try to change the type to US, all of the UK dates show up as "error".  Here is a screenshot of the column I am trying to fix.  My US dates have the format MM/DD/YYYY, my UK have the format DD-MM-YYYY (i.e. slashes for US and hyphens for UK).  Need some help with this one.

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like the attached file.

Please check the below attached pbix file and the code that is used in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSN9Q3MjAyUorViVYyNtQ1MNSFc03wyBmZonANLVG5hrooPGSDUEw1MkfoiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of SW" = _t]),
    Custom1 = Table.AddColumn(Source, "Result column", each if Text.Contains([Date of SW],"/") then 
 "en-US" else 
"en-GB"),
    #"Added Index" = Table.AddIndexColumn(Custom1, "Index", 1, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[#"Result column"]), "Result column", "Date of SW"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Pivoted Column", {{"en-US", type date}}, "en-US"),
    #"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"en-GB", type date}}, "en-GB"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type with Locale1", {"Index"}, "Attribute", "Result"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Result"})
in
    #"Removed Other Columns"

[Butters]

Worked perfectly.  Thank you so much!!