Powerquery US date Text to DateTime format

[TeisL]

Hi everyone,

I want to transform a text value (MM/DD/YYY hh:mm:ss) into a datetime (DD/MM/YYYY hh:mm:ss) value.

Example of the data:

Created date	New date
3/7/2019 1:25:03 PM	7/3/2019 13:25:03
3/18/2019 8:22:38 AM	18/3/2019 8:22:38

Thanks you so much!

[ronrsnfld]

You only need to tell Power Query the format of the text date.

If you want to add a column as you show in your example, then:

let
    Source = Table.FromColumns(
        {{"3/7/2019 1:25:03 PM","3/18/2019 8:22:35 AM"}},
        type table[Created date=text]
    ),
    #"Added Custom" = Table.AddColumn(Source, "New date", each DateTime.From([Created date],"en-US"), type datetime)
in
    #"Added Custom"

If you want to transform it in place, you can use the Change Type Using Locale from the right click dropdown on that column, and select DateTime and United States.

let
    Source = Table.FromColumns(
        {{"3/7/2019 1:25:03 PM","3/18/2019 8:22:35 AM"}},
        type table[Created date=text]
    ),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Created date", type datetime}}, "en-US")
in
    #"Changed Type with Locale"

In the Power Query editor, the date will be displayed according to your Windows Regional Settings.

In Power BI, you can set the format of that column however you wish.

[collinsg]

Good day TeisL,

You can use the DateTime.FromText() function. It has an optional parameter to specify the format of the input text and an optional parameter to specify the format of the output datetime, for example.

DateTime.FromText( [Datetime as Text], [Format="M/d/yyyy h:mm:ss tt", Culture="en-UK"] )

The following is an example using your dates. The first step, "Source" reproduces your table of datetimes in text format. The second step adds a column with "Created Date" converted to datetime in your desired format.

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31zcyMLRUMLQyMrUyMFYI8FWK1QGJG1pAJCysjIysjC0UHIESsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Created Date" = _t]),
  #"Convert from text" = Table.AddColumn(Source, "Datetime", each DateTime.FromText( [Created Date], [Format="M/d/yyyy h:mm:ss tt", Culture="en-UK"] ), type datetime)
in
  #"Convert from text"

Hope this helps.

[tackytechtom]

Hi @TeisL ,

How about this:

Here the Power Query M code for the new custom column:

try DateTime.FromText(
    Text.Split( Text.From( [date]), " " ){0} &" "&Text.Split( Text.From( [date]), " " ){1},[Format = "MM/dd/yyyy h:mm:ss"]) otherwise
    try DateTime.FromText(
    Text.Split( Text.From( [date]), " " ){0} &" "&Text.Split( Text.From( [date]), " " ){1},[Format = "M/dd/yyyy h:mm:ss"]) otherwise
    try DateTime.FromText(
    Text.Split( Text.From( [date]), " " ){0} &" "&Text.Split( Text.From( [date]), " " ){1},[Format = "MM/d/yyyy h:mm:ss"]) otherwise
   DateTime.FromText(
    Text.Split( Text.From( [date]), " " ){0} &" "&Text.Split( Text.From( [date]), " " ){1},[Format = "M/d/yyyy h:mm:ss"])

Note, I called the original column "date". You need to align the code accordingly. 

Also, I took the code from here and aligned it a bit:

Converting dates from MM/DD/YYYY to DD/MM/YYYY in ... - Microsoft Fabric Community

Hope this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/