PowerBI thinks my data is already in local time, instead of UTC

[wiselyman3]

I have a strange problem that I cant seem to figure out. I am connected to a Snowflake database that I have no control over. For a date/time column "EVENT_DATE",  the times are UTC. However, PowerQuery is bringing in the correct values, but incorrectly thinking that they are already my local time zone (EST). Therefore, trying to convert the values doesnt work, shown in my screenshot. For example, I know that for the first row, the time should be 7:55:27 am EST because I was the one who performed this task. But powerquery thinks its already been converted from UTC to EST. How can I fix this? 

[ronrsnfld · Accepted solution]

You will need to add this step in the Advanced Editor:

• Remove the existing timezone indicator to create just a datetime
• Then, optionally,  add a zone indicator indicating UTC

Example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtE3stA3MjAyVTA0tDI1tTIyV9A1MLEyMFCK1QHLmkJkLa0MTK0MLRUCfOHysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [EVENT_DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EVENT_DATE", type datetimezone}}),
    
    #"Change TimeZone" = Table.TransformColumns(#"Changed Type",{"EVENT_DATE", each 
                            [a=DateTimeZone.RemoveZone(_),
                             b=DateTime.AddZone(a,0)][b], type datetimezone})
in
    #"Change TimeZone"

  Changed Type

Change TimeZone

[wiselyman3]

What parts of this am I inserting, and where? Apologies I dont have much experience with the Advanced Editor. 

[wiselyman3]

This did work, but unfortunately now this has made powerbi load endlessly when trying to confirm my transformations. It just constantly loads rows now. Any thoughts on why this is happening?

[ronrsnfld]

You don't indicate to whom this post is addressed. (If you "Reply" on the post your replying to, the system will properly annotate your post). But if you are replying to my post, I'd need to at least see your code before I could hazard a guess. And I'm not sure what you mean by "confirm my transformations"

[wiselyman3]

By confirm I just meant when clicking Close and Apply. I did figure out the issue, thanks

[Nasif_Azam]

Hey @wiselyman3 ,

You're right in identifying that Power Query is misinterpreting your UTC EVENT_DATE values as already being in your local time (Eastern Time, UTC-4), which leads to incorrect conversions when you try to shift to local time explicitly.

Problem Summary

• The EVENT_DATE column from Snowflake contains timestamps in UTC.

• Power Query (Power BI) is assuming those timestamps are local time, so any conversion (e.g., using DateTimeZone.ToLocal) doesn't produce the correct result.

• Example: 2025-04-28 11:55:27 AM -04:00 is interpreted as EST (UTC-4), but should be 7:55:27 AM EST if the value were truly UTC.

Solution: Force Power BI to Interpret as UTC

You need to explicitly inform Power Query that the timestamp is UTC before converting it to your local time zone.

Here’s how you can fix this using Power Query M code:

Steps in Power Query Editor

1. Go to Power Query Editor.

2. Select the EVENT_DATE column.

3. Add a new custom column using this formula:

DateTimeZone.SwitchZone(DateTimeZone.From(Event_DATE), -4)

Better (Automatic) Version Using System Time Zone

If you want to automatically use your system time zone, do this instead:

DateTimeZone.ToLocal(DateTimeZone.From(Event_DATE))

Optional: Preview the Fix

You can also inspect the datetime using this test formula:

let
    utc = DateTimeZone.From([EVENT_DATE]),
    local = DateTimeZone.ToLocal(utc)
in
    local

This lets you preview if your values now match your actual local event times.

Things to Remember

You should not use DateTimeZone.ToLocal([EVENT_DATE]) directly unless you're sure Power Query has the correct time zone context for the original value which in your case, it doesn't.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[wiselyman3]

Hello Nasif, how does this solution account for Daylights Savings Time? For EST, the conversion will not always be -4 hours. There is also historical data that needs to be converted that will be -5 hours for daylights savings time. 

[Nasif_Azam]

Great point and you’re absolutely right that Eastern Time is not always UTC-4. It’s UTC-5 during Standard Time (fall/winter) and UTC-4 during Daylight Saving Time (spring/summer). So hardcoding -4 (as in DateTimeZone.SwitchZone(DateTimeZone.From([EVENT_DATE]), -4)) will not correctly account for historical timestamps that span both periods.

Use DateTimeZone.ToLocal with Proper UTC Assumption

Power Query doesn’t automatically know your column is in UTC. But once you tell it that, you can rely on DateTimeZone.ToLocal to correctly apply DST logic.

Here’s how to do it dynamically and accurately:

let
    Source = ... ,  // your source step
    AddUTCZone = Table.AddColumn(Source, "AsUTC", each DateTimeZone.From([EVENT_DATE])),
    ConvertToLocal = Table.TransformColumns(AddUTCZone, {{"AsUTC", each DateTimeZone.ToLocal(_), type datetimezone}})
in
    ConvertToLocal

Why This Works

• DateTimeZone.From(...) tells Power Query the value is in UTC (not local).

• DateTimeZone.ToLocal(...) then converts that UTC value to your machine's local time, applying DST automatically based on the date.

This approach ensures:

• April 28 gets converted using UTC-4 (because DST is active).

• December 28 would get converted using UTC-5 (no DST).

Inspect the Time Zone Offset

If you want to see how DST affects each value:

DateTimeZone.SwitchZone(DateTimeZone.ToLocal(DateTimeZone.From([EVENT_DATE])), 0)

This brings the final result back to UTC but retains the DST offset from your local zone. It's great for validation purposes.

[wiselyman3]

Hi Nasif, 

Apologies but I am very new to using the Advanced Editor. When I attempt to add this, I get a Token "," expected error. How and where would I add this so it avoids an error?

[SundarRaj]

Hi @wiselyman3
To convert your date into 7:55? That you are looking for? If that's the case, here's a solution. Let me know if that's what you are looking to do here. Thanks

Here's the code:
let
Source = #table(
{"EVENT_DATE"},
{
{#datetimezone(2025, 4, 28, 11, 55, 27, -4, 0)},
{#datetimezone(2025, 4, 28, 11, 42, 23, -4, 0)},
{#datetimezone(2025, 4, 25, 21, 5, 19, -4, 0)},
{#datetimezone(2025, 4, 25, 21, 0, 38, -4, 0)}
}
),
TextAfter = Table.TransformColumns(Source, {{"EVENT_DATE", each Text.AfterDelimiter(Text.From(_, "en-IN"), " "), type text}}),
Time = Table.AddColumn(TextAfter, "TIME", each Text.AfterDelimiter([EVENT_DATE], "-"), type text),
TextBefore = Table.TransformColumns(Time, {{"EVENT_DATE", each Text.BeforeDelimiter(_, " -"), type text}}),
Type = Table.TransformColumnTypes(TextBefore,{{"EVENT_DATE", type time}, {"TIME", type time}}),
#"Correct Time" = Table.AddColumn( Type , "CORRECT_TIME" , each Number.From ( [EVENT_DATE] ) - Number.From ( [TIME] ) ),
#"Changed Type" = Table.TransformColumnTypes(#"Correct Time",{{"CORRECT_TIME", type time}})
in
#"Changed Type"

[wiselyman3]

Unfortunately this will not always work, because I have historical and future data that will be in Daylight Savings Time for EST, so will not always be -4. For Daylights Savings time dates it will be -5, so I would need this to dynamically change from -4 to -5 based on the dates