Forum Discussion
CONVERT TIME FORMATS
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