Forum Discussion
Timezone conversion: handling daylightsavings time dynamically
i had the following problems with above approach.
1. When i refresh the report, the transpose in Table 1 is undone.
2. unable to change datatype to date time. I did not merge Time and Date, because neither allowed datatype conversion from text to datetime.
If above weren't issues my plan was to join Datetime column to any table where I need to make time conversion.
Web.Page is risky and PowerBI will enforce it to refresh through a gateway even when the data source it accesses is cloud based.
An alternative way to get local time dynamically is by calling an API and use Xml.Document to parse the response. This will avoid to use a gateway to refresh.
Here are the steps I figured out:
- Open your PBIX file and click "Edit Queries"
- In the query editor, choose the query used to get the local time. under "Home", click "Advanced Editor". In the editor window, replace your query with this one:
let Source = Xml.Document(Web.Contents("http://api.timezonedb.com/v2/get-time-zone?key=LGN45QRJHMMD&format=xml&by=zone&zone=PDT")),
Value = Source{0}[Value],
Value1 = Value{12}[Value]
in
Value
3. You will need to replace the highlighted key with your own key (to get your own key, you can register a free account from timezonedb.com, and also replace the zone with your time zone, e.g. for Texas/Dallas, you can use CDT for central daylight saving time).
4. Then update your report to use the result from this query as local time.
Hope this is helpful.
Yingwei