Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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:
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?
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
worked...thanks so much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.