Power Query Error on DateTimeZone Conversion

Question

Hi,

I'm trying to convert timezone for a column but I keep getting errored out as below:

Expression.Error: We cannot convert the value #datetime(2021, 6, 4, 10, 0, 0) to type DateTimeZone.
Details:
Value=6/4/2021 10:00:00 AM
Type=[Type]

Formula Used: = DateTimeZone.SwitchZone([Start Time], 0)

Details:
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{<!-- -->{"Change Request", type text}, {"Start Time", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTimeZone.SwitchZone([Start Time], 0))
in
#"Added Custom"

Answer 1

Hi @Devendra Gundecha

Your query code:

The error clearly indicates the reason: the type of the value ([Start Time]) isn't "good":

According to doc. function DateTimeZone.SwitchZone expects a value of type datetimezone but you provide a value of type datetime (your #"Changed Type" step):

So change your #"Changed Type" step with:

#"Changed Type" = Table.TransformColumnTypes(Source,  
    {{"Change Request", type text}, {"Start Time", type datetimezone}}  
),  

OR, if you want column [Start Time] to be of type datetime, then convert the value to type datetimezone as follow when adding your custom column:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each  
    DateTimeZone.SwitchZone(  
        DateTimeZone.From([Start Time]),  
        0  
    )  
)  

Hope this all makes sense & helps