mutliple time format issue

[anglefbi11]

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 

[anglefbi11 · Accepted solution]

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

[amustafa]

Try adding locale to your code. Like this example.

= Table.TransformColumnTypes(#"Promoted Headers", {{"DATE", type datetime}}, "en-US")

[anglefbi11]

dears

kindly look what happened when i change the column to date and time

[dufoq3]

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}})

[amustafa]

Please provide a sample data in a file you are importing into Power Query.

[amustafa]

Simply change the column data type to Date/Time...

= Table.TransformColumnTypes(Source,{{"DATE", type datetime}})

[anglefbi11]

i did and nothing change.