Date format

[Anonymous]

Hi,

I have a dataset that updates everyday and columns with dates in them, however the format of these dates changes now and then. For example, sometime the entire column might be in the "MM/DD/YYYY" format while sometimes the format might be "DD/MM/YYYY". I never know when the format is going to change so I can't just "Change Type" or "Change Type Using Locale". Is there some type of error catching I can do to change type using locale if change type doesn't work? I need 3 columns to be converted together.

Thanks!

[edhans]

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"

[Anonymous]

Hi @edhans,

Thank you, this is really helpful. However, the issue is with dates like 4/1/2020 like you mentioned. Is there anything you can think of to solve that?

[edhans · Accepted solution]

Not off of the top of my head. If you can come up with some sort of business logic, I can assist with the Power Query side, but cannot think of a way for PQ to just know when 4/1/2020 is Jan 4 or April 1.

[v-yingjl]

Hi @Anonymous ,

You can try to use format()  by Custom date and time formats:

FORMAT(<value>, <format_string>)  

Please refer:

1. Custom date and time formats for the FORMAT function 
2. Pre-defined date and time formats for the FORMAT function 

Best Regards,
Yingjie Li

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

[amitchandak]

@Anonymous , You can convert a DD/MM/YYYY date into MM/DD/YYYY like

date(year( right(DD__MM__YY[Version_Id],4)), month(mid(DD__MM__YY[Version_Id],4,2)) ,day(left(DD__MM__YY[Version_Id],2)))

But how would you identify that which format it is?

Or maybe all dates are in DD/MM/YYYY format is it just for days <=12 , it seems like it is MM/DD/YYYY