Forum Discussion
Convert UTC to client time zones?
Hi Betsy,
We may take a try with the method below:
Assuming currently we have the Time and UTC column in a table, where [Time] stores the actual UTC time, [UTC] stores the changed hour count between client and UTC.
Based on the different Time Zone, UTC values changes within -12 to 12 range.
When data loaded into Power BI, open Query Editor, under the Add column tab, click Add Custom Column, then under the formula part, copy and paste the following:
DateTime.AddZone([Time],0)Click OK. This step adds the default UTC Time Zone (0 here) under the time stored in [Time], to the newly created ZoneTime column;
After that, we need to create another column to switch the time zone to the client, also click on the Add custom column, then copy and paste the following formula:
DateTimeZone.SwitchZone([ZoneTime],[UTC])
After that, we have successfully added the time Zone information into the time. Then we need format the column into date/time/timezone format.
Select ClientTime column, navigate to Transform tab, choose data type and select Date/Time/TimeZone:
After that, click close and apply.
Till then we should have finished converting the time into Client local time.
If any further questions, please feel free to post back.Regards,
Charlie Liao
Hi Betsy,
We may take a try with the method below:
Assuming currently we have the Time and UTC column in a table, where [Time] stores the actual UTC time, [UTC] stores the changed hour count between client and UTC.
Based on the different Time Zone, UTC values changes within -12 to 12 range.
When data loaded into Power BI, open Query Editor, under the Add column tab, click Add Custom Column, then under the formula part, copy and paste the following:
DateTime.AddZone([Time],0)
Click OK. This step adds the default UTC Time Zone (0 here) under the time stored in [Time], to the newly created ZoneTime column;
After that, we need to create another column to switch the time zone to the client, also click on the Add custom column, then copy and paste the following formula:
DateTimeZone.SwitchZone([ZoneTime],[UTC])
After that, we have successfully added the time Zone information into the time. Then we need format the column into date/time/timezone format.
Select ClientTime column, navigate to Transform tab, choose data type and select Date/Time/TimeZone:
After that, click close and apply.
Till then we should have finished converting the time into Client local time.
If any further questions, please feel free to post back.
Regards,
Charlie Liao
I have followed this process successfully at the query level. However, when referencing the ClientTime in a report, the time zone information is stripped out and the fields revert to Date/Time from the Date/Time/Timezone format; times revert to UTC.
How can this data be used in a report? Is this a known issue with Power BI?
Having a similar issue, when I'm in the Query Editor and convert the column type to "Date/Time/Timezone" it shows the proper -05:00. However on the actual report as a Date/Time it shows UTC.
Hmm. I didn't have that issue, but I ran the client time as a one off report, so I deleted the UTC time column after I converted a client time replacement. I wonder if that makes a difference?
Hey ppfisterer,
Is your report in the service? I had no issues making the report using client time on the desktop, with the caveat that I created a time reference table with the hours listed, that I then related the client time transformation back to, That may help with the time conversion issue (I use both time and date reference tables to tie my various tables together. I found time, in the general sense, to not work very well in BI without them).
Betsy
Betsy,
yes, I have both time and date reference tables. I duplicated the UTC column in the souce table twice. In one column apply Date and in the second apply Time. The values there respect the time zone. If I then reference these new columns in a table in the report, they no longer reflect the adjusted time zone but show the UTC time again. The original field with Date/Time/Timezone format does look ok . Here are the steps from the query editor
= Table.DuplicateColumn(#"Added Custom2", "RoomTimeStamp", "RoomTimeStamp - Copy")
= Table.RenameColumns(#"Duplicated Column",{{"RoomTimeStamp - Copy", "RoomStartDate"}})
= Table.TransformColumnTypes(#"Renamed Columns",{{"RoomStartDate", type date}})
= Table.DuplicateColumn(#"Changed Type", "RoomTimeStamp", "RoomTimeStamp - Copy")
= Table.RenameColumns(#"Duplicated Column1",{{"RoomTimeStamp - Copy", "RoomStartTime"}})
= Table.TransformColumnTypes(#"Renamed Columns2",{{"RoomStartTime", type time}})
UTC = 11/19/2016 4:25:43 AM +00:00
for E. Australia Standard Time 10
RoomTimeStamp = 11/19/2016 2:25:43 PM +10:00
RoomStartDate = 11/18/2016 ??
RoomStartTime = 11:25:43 PM ??
The RoomTimeStamp is correct but when the values have types applied as Date or Time, the data doesn't make sense.
-Phil
Instead of applying a different Type to the column, a Transform to extract the date and time seems to do the trick.
