CONVERT TIME FORMATS

[Anonymous]

Hey Guys,

I have a column with 24 hour date time values like this

10/02/2020 21:18:39

Which parse fine.

How ever there are some values that look like this 

10/14/2020 10:16:02 PM

and power query is flagging them as errors with the following message

DataFormat.Error: We couldn't parse the input provided as a DateTime value.

How do i convert these values to the 24 hour format that power query can parse.

Kindly Note that I am pulling from a database that i only have read access to so cannot change the values at the source.

[Jimmy801]

Hello @Anonymous 

You could think of applying a manual check what format it is, and then applying it. In your case you could check if AM or PM is at the end of your datetime and then format it as "en-US" otherwise with "en-UK". Here the formula

if Text.EndsWith([DateTime], "AM") or Text.EndsWith([DateTime],"PM") then DateTime.FromText([DateTime], "en-US") else DateTime.FromText([DateTime],"en-UK")

Here a complete code example

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNzDSNzIwMlAwMrQytLAytlSK1QGLG5pAxA0NrAzNrAyMFAJ8lWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
#"Added Custom" = Table.AddColumn(Source, "New DateTime", each if Text.EndsWith([DateTime], "AM") or Text.EndsWith([DateTime],"PM") then DateTime.FromText([DateTime], "en-US") else DateTime.FromText([DateTime],"en-UK"), type datetime)
in
#"Added Custom"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[PC2790]

Looks like it is interpreting the date as dd/mm/yyyy.

Can you check if it is only happening with the dates having mm value as > 12.

Also, please check your Locale settings by clicking on the column and selecting option - "Using Locale".

It should show you the format it is expecting:

[Anonymous]

So it turns out the date is actually the issure like you said but i have selected to correct Date Time Locale which is UK but i still have those errors.

Any Ideas?

[PC2790 · Accepted solution]

That's where the issue lies.

See the expected format of Date for United Kingdom(dd/mm/yyyy):

However the date you have is in format-mm/dd/yyyy.

Can you try changing it to English(Unites States) and see if it fixes your issue?

Thankyou

[Anonymous]

worked...thanks so much