question

DevendraGundecha-9325 avatar image
0 Votes"
DevendraGundecha-9325 asked Lz-3068 commented

Power Query Error on DateTimeZone Conversion

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"

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Lz-3068 avatar image
1 Vote"
Lz-3068 answered Lz-3068 commented

Hi @DevendraGundecha-9325

Your query code:

102383-querycode.png

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

102384-repro.png


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

102385-datettimezonedoc.png


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


querycode.png (25.7 KiB)
repro.png (20.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DevendraGundecha-9325. How are things going with the above proposal?

0 Votes 0 ·