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
collinsg's avatar
collinsg
Solution Sage
2 years ago

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.