Date format

Try this in Power Query Anonymous 

This will convert all text to dates (format the dates as text first, or just delete any prior change type for that column as PQ will assume text for flat files) and then count the errors. If there are errors, then it will convert using a localization.

The way it works is it counts the number of errors in the previous step that have a generic Date.FromText([Date]) conversion. If there are no errors, then it leaves it at that.

If there are errors, then it switches to a localization conversion, Date.FromText([Date], "en-BS") which is English/Bahamas. You'll need to pick your own localization. The easiest way to do that is do it manually on a column then go steal the nn-XX localization code PQ generates.

There is a problem with this method. If your dates are just a few, it is possible to get it wrong for example, a bunch of rows like 5/1/2020, 4/1/2020, 3/1/2020, etc. will not generate errors. In US format, that is just May 1, April 1, and March 1, but in Europe, that is Jan 5, 4, 3.

But if that is the case, then it won't know. However, if you later import more data and you get 18/1/2020, it will switch the ENTIRE COLUMN on that refresh to the localized format.

Here is what it looks like (I used another dataset to play with here)

Put this code in a blank query:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc87DsIwDIDhu2RGws84GQviFYSgpQIE6v2vgU2XSizxP3xyks8nga4B1wQEaZX6FyIOHqeh5Np7VNXioxhhmlbO84I/OkNoHpsnYw7O5AdK0RnbAo8HqbDzaG8l3nhkVj/NcvlppIVuo0E9xeqGInsPQuAYReaXIC/49Vznh3f7ynbwUGOJO7LBP78fVeXocWmi9gpeamw3Ifvnt21mfsYf3ob4CA5iMQg5TdMX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"Dimension 1" = _t, #"Dimension 2" = _t, #"Measure 1" = _t, #"Measure 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Dimension 1", type text}, {"Dimension 2", type text}, {"Measure 1", Int64.Type}, {"Measure 2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", 
        each 
            if Table.RowCount(
                Table.SelectRowsWithErrors(
                    Table.AddColumn(#"Changed Type", "Custom", each Date.FromText([Date]))
                    )
                ) > 0 
            then Date.FromText([Date], "en-BS") 
            else Date.FromText([Date])
        )
in
    #"Added Custom"