CODEWITHSUNDEEP
powerbipowerquery
AverageAsker
AverageAsker

Reputation: 188

Power Query: Date Time Zone operations

I am building Power BI report based on data from SQL Server's table User which contains a column CreatedDate of type DateTypeOffset(7). I am looking for Power Query equivalent to .NET [TimeZoneInfo.FindSystemTimeZoneById]. My Report data has a Time Zone info which is stored as standard ID (like Pacific Standard Time) and I need to convert values in my DateTimeOffset column to the time zone I was talking about above, this is how I do that in C#:

TimeSpan timeZoneOffsetSpan = TimeZoneInfo.FindSystemTimeZoneById(settings.TimeZ​one).GetUtcOffset(UserCreatedDateTime);

This gives me a difference between the time in the time zone stored in my Settings and UTC for UserCreatedDateTime. And I am executing that line of the code just once for all rows in my User table because the purpose of the code above is to find out current offset taking into account such timezone's features like DayLightSaving.

And now I can simply add that offset (which could be positive or negative) against every value in my User.CreateDateTime column, in C# I am doing that by using [DateTimeOffset.ToOffset]:

DateTimeOffset convertedOffset = UserCreatedDateTime.ToOffset(timeZoneOffsetSpan)

So I need to be able to do the same conversions in my Power BI report but I couldnot find any Power Query function which can do the same what [TimeZoneInfo.FindSystemTimeZoneById] does, if that function existed it would cover my first line of C# code and then I would need to find an equivalent to [TimeZoneInfo.FindSystemTimeZoneById]. [DateTimeZone.SwitchZone] is probably what I need but I first need to know the offset corresponding to my time zone and then I would be able to supply that offset as 2nd and 3rd parameters to that function.

So, to finalize, I need Power BI analog for [TimeZoneInfo.FindSystemTimeZoneById]. Can anybody help?

Upvotes: 3

Views: 4815

Answers (2)

Trubs
Trubs

Reputation: 3042

You can take advantage of the AT TIME ZONE feature of TSQL to create a table of offsets (in minutes) for each day, for each timezone, and import the resultant table into PowerBI, so you can add/subtract relevant offsets to your base time.

If I was in the OP's situation, I would import (convert) all times to UTC, and then add the offset within PowerBI, dependent on the timezone you want to display. (maybe using this method?)

The example below gives a table the offsets for Australian timezones against UTC.

If my target table is in UTC and I want to change the time in PowerBI, I just have to add the specific offset to my time...

+------------+-----------+----------+----------+----------+ 
| TargetDate | QLDOffset | NTOffset | SAOffset | WAOffset |
+------------+-----------+----------+----------+----------+ 
| 2018-04-02 |      -600 |     -570 |     -570 |     -480 | 
| 2018-04-01 |      -600 |     -570 |     -570 |     -480 | 
| 2018-03-31 |      -600 |     -570 |     -630 |     -480 | 
| 2018-03-30 |      -600 |     -570 |     -630 |     -480 |
+------------+-----------+----------+----------+----------+

You can see the offset changing for SA on 1st April

WITH MostDays AS ( --MostDays is a table of 10 years worth of dates going back from today.  
               SELECT Today = CAST(DATEADD(DAY, -days, GETUTCDATE())  AS DATETIMEOFFSET)
               FROM (SELECT TOP 3650 Days = ROW_NUMBER() OVER (ORDER BY message_id )  -- this will give a rolling window of 10 years
                     FROM sys.messages
                    )x
            )
--  This section takes the datetime from above, and calculates how many minutes difference there is between each timezone for each date.
Select  TargetDate=CAST(Today AS DATE)
    ,QLDOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'E. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,NTOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'AUS Central Standard Time' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,SAOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'Cen. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
    ,WAOffset = DATEDIFF(minute,CAST(Today at TIME ZONE 'UTC' AT TIME ZONE  'W. Australia Standard TIME' AS DateTime),CAST(Today at TIME ZONE 'UTC' AS DATETIME))
FROM MostDays

Obviously the table gets pretty big if you have no limits on dates or timezones

Upvotes: 0

teylyn
teylyn

Reputation: 35990

You could add a table to the data model with columns for time zone ID and time zone offset. In your main query you can join this table and then create a calculation to add or subtract the offset into a new column. After that, remove the joined column.

See here How can I perform the equivalent of AddHours to a DateTime in Power Query?

Or you could use the DateTimeZone functions built into M.

Upvotes: 0

Related Questions