If you work outside the GMT time zone, you may have noticed a time discrepancy between Power BI Desktop and Power BI Service. We definitely did. This article is based on what we learned and what we did to solve the issue.
The issue
Let’s say you’re using Power BI to visualize your timesheet information. When you report against time while using Power BI Desktop, your time is shown in your local time zone. However, when you publish that same report on Power BI Service, the time is shown in Greenwich Mean Time (GMT). This is by design.
You could manually adjust your PowerBI to subtract or add the necessary number of hours…however, if your timezone is subject to Daylight Savings, your report will drift by an hour after the time change.
Our solution
There are a few ways to correct this challenge. We solved ours at load time with Power Query and a freely accessible timezone service called TimeAPI (https://timeapi.io/). Here are the steps we took:
1. We created a custom PowerQuery M query to pull the needed timezones (in our case, Canada/Pacific and Europe/Stockholm) from TimeAPI:
PowerQuery M
/* Import Timezones from timeapi.io */
let
Source2 = Json.Document(Web.Contents(“https://timeapi.io/api/TimeZone/zone?timezone=Europe/Stockholm”)),
#”Converted to Table” = Table.FromRecords({Source1} & {Source2}),
#”Expanded currentUtcOffset” = Table.ExpandRecordColumn(#”Converted to Table”, “currentUtcOffset”, {“seconds”}, {“currentUtcOffset.seconds”}),
#”Expanded dstInterval” = Table.ExpandRecordColumn(#”Expanded currentUtcOffset”, “dstInterval”, {“dstName”}, {“abbreviation”}),
#”Added offset_hours” = Table.AddColumn(#”Expanded dstInterval”, “offset_hours”, each ([currentUtcOffset.seconds]) / 3600, Int64.Type),
#”Renamed Columns” = Table.RenameColumns(#”Added offset_hours”,{{“timeZone”, “timezone”}})
in
2. We added a GmtOffset custom function to the top of each query that loads a dataset with date/time fields:
PowerQuery M
let
…
3. We adjusted each date/time field to the desired timezone using DateTimeZone.SwitchZone and used GmtOffset to fetch the current hourly offset:
PowerQuery M
…
#”Added Local.Updated” = Table.AddColumn(#”Previous statement”, “Local.Updated”, each DateTimeZone.SwitchZone([DATE_FROM_DB],GmtOffset(“Canada/Pacific”)), type datetimezone),
…
After, all the dates/times were loading in the desired timezone and displaying correctly on both PowerBI Desktop and PowerBI Service.