The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
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
Solved! Go to 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"
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
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"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
107 | |
75 | |
61 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |