Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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?
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
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.