4

(Here's quite the same question I opened on Microsoft Power BI community forum)

I have a table in my database where I have a column with datetime values in UTC and another column with the timezone of this datetime (a string containing the IANA id of the timezone, like "America/Sao_Paulo").

Table print, with a datetime column named "Start" and a text column named "Timezone Id"

I must generate in Power BI a new column with the DateTimeZone value corresponding to the given datetime in UTC converted to the given timezone, and it must work on Power Bi Desktop and also on cloud (Power BI embedded).

I searched through Power Query M and DAX docs, but could only find ways to apply timezone to a datetime using numbers (e.g. -10), but not using IANA IDs... (It's important for me to use the timezone id instead of simple numbers because of Daylight Saving Time).

I tried Reza Rad's idea of using a Web Query with Power Query in the past, but it turns out that online Power BI won't let me use web query and simply won't update my data source (which is a hard requirement to me). Furthermore, their method doesn't solve my exact situation, as I should still figure out which website to send request and which structure to use on my report. (I'm open to suggestions on this matter)

There's a similar thread on Power BI community forum, still with no solution.

Could someone help me apply the timezones to the datetimes, be it using Power Query M, DAX or other workarounds on Power BI?

Leo von Barbarosa
  • 727
  • 8
  • 22
  • Why not add an excel table with your id_s (gotten form internet) and a column of the time difference? – Aldert Jul 30 '19 at 07:47
  • @Aldert the problem is the Daylight Saving Time... Most timezone have 2 different offsets, depending on the day and year, so I'd have to make a big list with [TimezoneID], [startDate], [endDate], [offset]; two rows per year, because on every year it's a different date of offset transition – Leo von Barbarosa Jul 30 '19 at 14:01

3 Answers3

2

Question:

but it turns out that online Power BI won't let me use web query and simply won't update my data source (which is a hard requirement to me).

You need a gateway connection for the Online Power BI to work. In Online Power BI, the schedule refresh needs a gateway to refresh Web-Queries or local Folder/File Queries.

Procedure:

You need to install Data Gateway on your PC from here

I think in your particular case, you can install the Personal Gateway.
Instructions to install personal gateway: https://www.youtube.com/watch?v=xjcO5tNvjGs

Once you install it, Please follow the following steps:

  1. Go to the dataset settings in your Power BI Service and click on Gateway connection, your personal gateway will be automatically selected.
  2. After that edit your Data Source credentials and choose Anonymous.
  3. Once this is done, you can toggle the Schedule refresh on for your dataset.

Screenshot below:
Personal Gaateway

Note: I've tested this and its working fine.

Gangula
  • 5,193
  • 4
  • 30
  • 59
  • it's not clear to me how it solves my issue... Is it to skip the problem with the online web query? – Leo von Barbarosa Jul 30 '19 at 14:05
  • You mentioned, Online Power Query doesn't allow the use of Web-query. This is why it doesn't. You need a gateway connection for the Online Power Query to work and Refresh the dataset – Gangula Jul 30 '19 at 14:27
  • thanks for the hint! I'm still reluctant with this approach, because I still have to figure out which website to use and which structure to create on my report to finally solve my problem (the Reza Rad's idea I mentioned doesn't exactly solve my case, it only gives me a clue) – Leo von Barbarosa Aug 07 '19 at 14:37
0

If you'd like to get the information of the timezone as a query, the you can use the following Wikipedia link as the data-source and extract the timezone table from it.

I have tried it and it gets the data (Screenshot below). Once you get the data as query, you can create relationships or you can merge this with your query.

Please check if this helps you.

Timezone Database

Gangula
  • 5,193
  • 4
  • 30
  • 59
  • I really appreciate your help @Gangula! Sadly, the issue with DST is a complex one, it's not only a matter of knowing the offset, but which exact date will it be shifted... For example, here in Brazil it always happen on a different day every year, always on the weekend. Last year the president changed the usual date/month via law, and this year the new president canceled the DST, so it's something that depends on a lot of stuff I can't predict. I know there might be a website which gives me the precise info I need, I just haven't found it yet – Leo von Barbarosa Aug 08 '19 at 13:16
  • I know there's a way to apply timezone by name on .NET framework (I've coded it before). My biggest wish was they implemented something similar on DAX or Power Query (I even opened the question on Power BI community forum to see if they help me or add this (probably) new functions on the software) – Leo von Barbarosa Aug 08 '19 at 13:19
0

I would not connect to a web source to retrieve this data. If you know the offsets, you can make use of a script like the following (source: https://gorilla.bi/power-query/last-refresh-datetime/)

let
  UTC_DateTimeZone = DateTimeZone.UtcNow(), 
  UTC_Date         = Date.From(UTC_DateTimeZone), 
  StartSummerTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday), 
  StartWinterTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday), 
  UTC_Offset       = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1, 
  CET_Timezone     = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
  CET_Timezone

The most important parts are making use of

Rick de Groot - gorilla.bi
  • 196
  • 2