Power BI: Convert UTC to Eastern Time in Power Query with Daylight Saving Adjustments

Dealing with time-based data, especially time zones and daylight saving time, in Power BI and Power Query can be frustrating. There is no simple or built-in function to correctly convert a timestamp from UTC (Coordinated Universal Time) to local time while accounting for daylight saving time. While Power BI Desktop correctly displays DateTime.LocalNow() and DateTime.FixedLocalNow() based on the user’s machine, the timestamps lose their time zone information when the report is published to the Power BI service. This happens because Power BI adjusts the time based on the localized time of the machine during a refresh. Since the Power BI service operates in the cloud, it defaults to UTC as the standard time.

Displaying timestamps in UTC is not ideal. Most users will find it confusing and expecting them to mentally convert time zones is impractical. While I’ve come across several proposed solutions online, many have logic errors related to daylight saving time or require external API calls or scraping from other sites. In this demonstration, we’ll use a custom Power Query function to convert a timestamp to Eastern Time, correctly adjusting for daylight saving time. While this solution is specific to U.S. Eastern Time, it can easily be adapted for other time zones.

Instructions

Step 1: Create a New Power Query Function

In the United States, daylight saving time starts on the second Sunday in March and ends on the first Sunday in November. The time change occurs at 2:00 a.m. local time.

The SwitchZoneET function accepts a datetimezone parameter. First, the function converts the supplied argument to UTC and extracts the year. This year is then used to calculate the second Sunday in March and the first Sunday in November using Date.DayOfWeek and some math with the #duration function. Internally, the function works with UTC timestamps for consistency. To determine daylight saving time start and end, we add UTC-based time components, e.g., 7:00 a.m. UTC for daylight saving start and 6:00 a.m. UTC for daylight saving end. These correspond to 2:00 a.m. Eastern Time.

Given the original argument adjusted for UTC, we compare it to the calculated daylight saving time start and end dates/times. If the argument falls between these dates/times, the time zone offset is -4 hours (Eastern Daylight Time). If it falls outside, the offset is -5 hours (Eastern Standard Time).

NOTE: This function only handles conversions and adjustments for Eastern Time. If you want to use it for other time zones with the same rules (second Sunday in March and first Sunday in November), you would need to adjust the OFFSET_HOURS calculation accordingly.

1. From the Home ribbon in Power Query, select New Source > Other Sources > Blank Query. This creates a new blank query.
2. From the Home ribbon, click Advanced Editor.
3. Replace the default query with the following function:

let
  SwitchZoneET = (dateTimeZone as datetimezone) =>
    let
      dtz_utc = DateTimeZone.ToUtc(dateTimeZone), 
      dtz_utc_year = Date.Year(dtz_utc), 
      MARCH_START = #date(dtz_utc_year, 3, 1), 
      MARCH_START_DAY_OF_WEEK = Date.DayOfWeek(MARCH_START, Day.Sunday), 
      DST_START_DATE = MARCH_START
        + (
          if MARCH_START_DAY_OF_WEEK > 0 then
            #duration(14 - MARCH_START_DAY_OF_WEEK, 0, 0, 0)
          else
            #duration(7, 0, 0, 0)
        ), 
      DST_START_DTZ_UTC = DateTimeZone.ToUtc(
        #datetimezone(
          Date.Year(DST_START_DATE), 
          Date.Month(DST_START_DATE), 
          Date.Day(DST_START_DATE), 
          7, 
          0, 
          0, 
          0, 
          0
        )
      ), 
      NOVEMBER_START = #date(dtz_utc_year, 11, 1), 
      NOVEMBER_START_DAY_OF_WEEK = Date.DayOfWeek(NOVEMBER_START, Day.Sunday), 
      DST_END_DATE = NOVEMBER_START
        + (
          if NOVEMBER_START_DAY_OF_WEEK > 0 then
            #duration(7 - NOVEMBER_START_DAY_OF_WEEK, 0, 0, 0)
          else
            #duration(0, 0, 0, 0)
        ), 
      DST_END_DTZ_UTC = DateTimeZone.ToUtc(
        #datetimezone(
          Date.Year(DST_END_DATE), 
          Date.Month(DST_END_DATE), 
          Date.Day(DST_END_DATE), 
          6, 
          0, 
          0, 
          0, 
          0
        )
      ), 
      OFFSET_HOURS = 
        if (dtz_utc >= DST_START_DTZ_UTC and dtz_utc < DST_END_DTZ_UTC) then
          -4
        else
          -5, 
      dateTimeZoneET = DateTimeZone.SwitchZone(dtz_utc, OFFSET_HOURS)
    in
      dateTimeZoneET
in
  SwitchZoneET

4. Click the Done button.
5. In the Query Settings pane, under Properties, rename the query to SwitchZoneET.
6. Click Close & Load to exit Power Query.

Step 2: Testing the Function

In 2025, U.S. daylight saving time begins on March 9 and ends on November 2.

To verify that the function performs as expect on those particular days, we’ll create a test query that executes the function for every hour in each day. This query generates a list from 0 through 23 and then passes each value to SwitchZoneET for March 9 (VERIFY DST START) and November 2 (VERIFY DST END).

let
  Source = List.Generate(() => 0, each _ <= 23, each _ + 1), 
  #"Converted to Table" = Table.FromList(
    Source, 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ), 
  #"Renamed Columns" = Table.RenameColumns(
    #"Converted to Table",
    {{"Column1", "DAILY HOURS"}}
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Renamed Columns",
    {{"DAILY HOURS", Int64.Type}}
  ), 
  #"Added VERIFY DST START" = Table.AddColumn(
    #"Changed Type", 
    "VERIFY DST START", 
    each SwitchZoneET(#datetimezone(2025, 3, 9, [DAILY HOURS], 0, 0, - 5, 0))
  ), 
  #"Added VERIFY DST END" = Table.AddColumn(
    #"Added VERIFY DST START", 
    "VERIFY DST END", 
    each SwitchZoneET(#datetimezone(2025, 11, 2, [DAILY HOURS], 0, 0, - 4, 0))
  )
in
  #"Added VERIFY DST END"

As expected, the function returns the correct result for Eastern Standard Time and Eastern Daylight Time with the appropriate time zone offset occurring at the 2:00 a.m. local time.

Results

To further demonstrate the function:

1. From the Queries pane in Power Query, select SwitchZoneET.
2. A prompt will appear asking for a parameter. For dateTimeZone, enter 1/1/2025 09:00 AM -05:00. The function call will look like this:

let
    Source = SwitchZoneET(#datetimezone(2025, 1, 1, 9, 0, 0, -5, 0))
in
    Source

3. Click the Invoke button.
4. The result of the function will appear as a new query named Invoked Function in the Queries pane, returning the value 1/1/2025 9:00:00 AM -05:00 as expected.

If I instead entered the value 3/21/2025 07:30 PM -00:00, the corresponding function call returns the value 3/21/2025 3:30:00 PM -04:00 as expected.

let
    Source = SwitchZoneET(#datetimezone(2025, 3, 21, 19, 30, 0, 0, 0))
in
    Source

Summary

Managing time-based data with time zones and daylight saving time in Power BI and Power Query can be challenging, especially when converting UTC to local time. Using a custom Power Query function allows for accurate conversions to a particular time zone with adjustments for daylight saving time. This method is adaptable for other time zones with similar rules and provides a reliable solution that does not use external APIs or complex workarounds.