DateTimeZone loosing Zone info after custom function

[thomasronn]

I have a simple sql table with a datetime field which I want in local time. Converting it by changing type to datetimezone and then use ToLocal work fine. 

But when I also use a custom function to add an extra column the time is no longer changed to local time.

What am I doing wrong?

let
    Source = Sql.Databases("localhost"),
    PowerBI = Source{[Name="PowerBI"]}[Data],
    dbo_Table_1 = PowerBI{[Schema="dbo",Item="Table_1"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Table_1,{{"Created", type datetimezone}}),
    #"Calculated Local Time" = Table.TransformColumns(#"Changed Type",{{"Created", DateTimeZone.ToLocal, type datetimezone}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Calculated Local Time", "Type2", each add2([Type]))
in
    #"Invoked Custom Function"

Wiithout the #"Invoked Custom Function" = Table.AddColumn(#"Calculated Local Time", "Type2", each add2([Type])) line I get "Created" in local time, with the line inserted "Created" is utc.

The custom function is just a dummy function for testing that add 2 to inputvalue

let
    Source = (val as number) => val + 2
in
    Source

[thomasronn]

More info..

using the sql profiler I can see that the sql statements generated by power bi differs.

Without the custom function inserted the sql statement is

select [_].[EntryNo] as [EntryNo],
    [_].[Created] as [Created],
    [_].[Type] as [Type],
    convert(datetimeoffset, [_].[Created]) as [t0_0]
from [dbo].[Table_1] as [_]

With custom function

select [$Table].[EntryNo] as [EntryNo],
    [$Table].[Created] as [Created],
    [$Table].[Type] as [Type]
from [dbo].[Table_1] as [$Table]

so somehow powerbi desides that the datetimeoffset info isn't needed when a custom function is used. Why? 

[v-jiascu-msft]

Hi @thomasronn,

The cause could be the custom function. You add date with number, which couldn't work. I tried this function. It worked. You can try it out.

let
    Source = (val as datetimezone) => Date.AddDays(val, 2)
in
    Source

Best Regards,

Dale

[thomasronn]

Thanks, but my custom function is not using the datetime. I use a custom function on a number column.

The strange thing is that when I have 

Table.TransformColumnTypes(dbo_Table_1,{{"Created", type datetimezone}})

the sql statement is changed into a statement with 

convert(datetimeoffset, [_].[Created]) as [t0_0]

 which make sense.

But if I also have a custom function the Convert(datetimeoffset..) is gone. Which is wrong.

I actually managed to get the convert(datetimeoffset...) in again by also transform another column from whole number to decimal number. So to me it looks like a bug in the optimizing of the sql statement.