Forum Discussion

TeisL's avatar
TeisL
Frequent Visitor
2 years ago

Powerquery US date Text to DateTime format

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...
Need Help
Show and Tell
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

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.