Convert a columns of datatype datetimezone to datetime

Hi h4tt3n 

Convert into datetime but add another step

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNbDQNTIwMlAwsLAyMAAiBW0DIyClFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type datetime}})
in
    #"Changed Type1"

Hi h4tt3n ,

You can acheive this by splitting the column.Below i have attached the screenshots for your understanding.

This is your orignal column with Datetimezone

Split Column

 Split column:

Select or enter delimeter : Custom

Use +

Split at: Left-most delimeter

Click on OK

Result :

Result,which is a datetime datatype.

If your problem is solved please accept this as solution.

Thank you

First convert column to Date/Time/Zone and then here's my solution in PowerQuery:

Table.TransformColumns(#"PREVIOUS STEP NAME",{{"FIELD NAME", each if _=null then null else DateTime.From(_)+#duration(0,Number.From(Text.Start(Text.End(Text.From(_),6),3)),0,0), type datetime}})

Unsure if this is helpful to anyone. I was finding the solution getting the most kudos was substracting the timezone and this was the opposite of I wanted it. I ended up just making this stupidly long calculation instead:

if [DateTimeZone] is null then null else
DateTime.From(Text.BeforeDelimiter(Text.From([DateTimeZone], "en-AU"), "+"))
+
#duration(0,
Time.Hour(Time.From(Text.AfterDelimiter(Text.From([DateTimeZone], "en-AU"), "+"))),
Time.Minute(Time.From(Text.AfterDelimiter(Text.From([DateTimeZone], "en-AU"), "+"))),
Time.Second(Time.From(Text.AfterDelimiter(Text.From([DateTimeZone], "en-AU"), "+"))))

First part just makes sure the value isn't null.

Then it extracts the text before the delimiter "+" which in my case is the datetime, then adds the text after the "+" delimiter which is my timezone information.

Not exactly convient but does the job.

Don't forget to convert the column to a datetime once done.

EDIT: I've just noticed that this is almost 5 years old topic, but maybe it will help to someone...

Hi h4tt3n, check this:

let
    Source = #table(null, {{"07-08-2020 08:00:00 +02:00"}}),
    Ad_CorrectDateTime = Table.AddColumn(Source, "CorrectDateTime", each let a = DateTimeZone.From([Column1]) in DateTime.From(a) + #duration(0, DateTimeZone.ZoneHours(a), DateTimeZone.ZoneMinutes(a), 0), type datetime)
in
    Ad_CorrectDateTime

If there is a space after + sign (as you showed us in your sample) use this:

let
    Source = #table(null, {{"07-08-2020 08:00:00 + 02:00"}}),
    Ad_CorrectDateTime = Table.AddColumn(Source, "CorrectDateTime", each let a = DateTimeZone.From(Text.Combine(Text.Split([Column1], " + "), " +")) in DateTime.From(a) + #duration(0, DateTimeZone.ZoneHours(a), DateTimeZone.ZoneMinutes(a), 0), type datetime)
in
    Ad_CorrectDateTime