Forum Discussion

thomasronn's avatar
thomasronn
Resolver I
8 years ago

DateTimeZone loosing Zone info after custom function

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

 

Need Help

3 Replies

  • thomasronn's avatar
    thomasronn
    Resolver I
    8 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      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

      Date_Time_Zone_loosing_Zone_info_after_custom_function

      Best Regards,

      Dale

      • thomasronn's avatar
        thomasronn
        Resolver I
        8 years ago

        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.