Is there a way to easily convert this date format?

[rpmm]

I have a JSON file where I have a date field formatted as such:

Tue Nov 01 20:53:41 +0000 2022

When I try converting column to Date/Time or Date/Time/Timezone it throws an error and I can imagine so due to the format of the value. 

Is there a way to change the format in the expression for the "created_at" field to associate this format? 

= Table.TransformColumnTypes(#"Removed Columns",{{"created_at", type datetimezone}})

Romit. 

[danextian · Accepted solution]

Hi @rpmm ,

The parsing returns  an error because the string is not in the expected datetimezone format. It should be date + time  + timezone but the order in your sample is incorrect.  Try this as a custom column in Power Query:

let 
yr = Text.End([Column1],4),
time = Text.Middle([Column1],11,8),
date = Text.Middle([Column1], 4, 6),
tz = Text.BetweenDelimiters([Column1], "+", " ")
in DateTimeZone.From( Text.Combine({date, yr, time }, " ") & "+" & tz)

[rpmm]

Thanks! This worked perfectly!