UNIX to datetime with local zone

[AndresEsteban]

Hello

I have the following problem. I am importing data from azure postgresql from telemetry where the datetime data is in UNIX format so it is a bigint and is in UTC+00. When converting them to datetime power bi understands that they are in my zone and converts them as if they were UTC+2:00 but in reality they are still in UTC+00. I show you the problem: 

• Bigint UNIX: 1696120272833
• GMT: Sunday, 1 October 2023 0:31:12.833
• Your time zone: domingo, 1 de octubre de 2023 2:31:12.833 GMT +2:00

but power bi interprets that 0:31:12.883 as my UTC+2:00 time zone.

Also, when I do the transformation from UNIX to datetime I cannot add +2 because half the year is UTC +2:00 and the other half is UTC +1:00.

Does anyone know how I can fix it? I would need that when going from unix to datetime it would be UTC+00 so that later I could transform that column to my local time zone and add +1 or +2 depending on the time of the year.

Thank you

[AndresEsteban]

Thank you for the answer, it solves my problem. I also found another solution which I will leave here. https://community.fabric.microsoft.com/t5/Desktop/Convert-UTC-to-client-time-zones/m-p/56337

[lbendlin · Accepted solution]

use #datetimezone() instead of #datetime

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSzNDM0MjAyN7IwNlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #datetimezone(1970,1,1,0,0,0,0,0)+#duration(0,0,0,[Column1]/1000),type datetimezone)
in
    #"Added Custom"