Forum Discussion
mutliple time format issue
hi dears
i am using power query and i am facing a complicated issue with time format. i have a data with mixed 12 hours, 24 format . i need to know how to get them all as the same format . i treid using spilit and extract function but with no luck.
some of the date format is listed like:
02/12/2024 23:14
and some of them like
2/13/24 12:35 AM
you can notice that year in the first date is YYYY and time is 24 format. in the second date the year is YY and timing is 12 hrs format
kindly find the below
hi guys
thanks god i found the soulution finally . i changed my regional format in my pc from english UK to English US and it has been solved
7 Replies
Simply change the column data type to Date/Time...
= Table.TransformColumnTypes(Source,{{"DATE", type datetime}})
i did and nothing change.
Please provide a sample data in a file you are importing into Power Query.
dears
kindly look what happened when i change the column to date and time
This will solve your issue. Add as custom column:
try DateTime.FromText([Date], "en-US") otherwise DateTime.FromText([Date], [Format="M/d/yy hh:mm tt", Culture="en-US"])If you want to transform existing [Date] column, add this as new step (just replace Previous_Step😞
= Table.TransformColumns(Previous_Step, {{"Date", each try DateTime.FromText(_, "en-US") otherwise DateTime.FromText(_, [Format="M/d/yy hh:mm tt", Culture="en-US"]), type datetime}})
Try adding locale to your code. Like this example.
= Table.TransformColumnTypes(#"Promoted Headers", {{"DATE", type datetime}}, "en-US")
hi guys
thanks god i found the soulution finally . i changed my regional format in my pc from english UK to English US and it has been solved
