Power BI is automatically converting UTC time to local time

[cesarvinas]

I have data in a CSV file that has been exported from Salesforce. The CreatedDate column in the file has date/time values like this "2020-03-08T02:06:14.000Z". All this values are in UTC time zone.

When I import this data into Power BI Desktop, the values get automatically converted to my local time which is EST so "2020-03-08T02:06:14.000Z" becomes "3/7/2020 9:06:14 PM". The conversion is all correct, but I DO NOT want Powe BI to make it.

How do I tell Power BI to leave the values in UTC? I've been searching for ours and have found a lot of info about converting time zones, but nothing that explains how to avoid Power BI converting values automatically.

[rachaelwalker]

I am having a similar issue. The easiest way I know how to work around it, is by extracted the timezone offset text from the end and then convert to Date/Time. The UTC time did not convert to local. 

=each Text.BeforeDelimiter(Text.From(_, "en-US"), "+"), type text}})

UTC Date/Time

[rdg515]

This was frustrating me too but I found a solution.  Create a custom column to do the conversion.  The following formula converts the text field to a datetimezone data type, then drops the zone.  Once the zone is dropped the conversion to DateTime will not attempt to convert to local time.

DateTime.From(DateTimeZone.RemoveZone(DateTimeZone.FromText([CreatedOn]))))

Insipired by: https://community.powerbi.com/t5/Desktop/Keep-UTC-00-00-in-time-stamp/m-p/317428

[Syndicate_Admin]

I share what worked for me

TodaysDate = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-6),

#"Converted to Table" = #table(1, {{TodaysDate}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Actualización de datos"}}),

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Actualización de datos", type datetimezone}})
in
#"Changed Type"

[v-yingjl]

Hi @cesarvinas ,

1. Open Power Query Editor
2. Choose Change types in APPLIED SETPS
3. Change the data type from data/time to date/time/timezone and replace current changes

You can also refer the simlar case:

https://community.powerbi.com/t5/Desktop/TimeZone-problems/td-p/171245

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[cesarvinas]

Thank you, @v-yingjl. I had tried this approach, but unfortunately PBI still automatically converted values into local time. I still needed to use DateTimeZone.ToUtc to force PBI to get values back to UTC. Very counterintuitve to be honest. If the data in the source is already UTC and includes Date, Time, and Zone, PBI shouldn't automatically convert to local. Also, even in this case, if the data type is switched to DateTimeZone, at least in this case PBI should stop converting values to local.

[Greg_Deckler]

https://docs.microsoft.com/en-us/powerquery-m/datetimezone-toutc

[saralsaral]

I faced a similar issue, may I ask why this is done? Why would a piece of software convert my data into something else without even a warning? Where is the documentation for this silent transformation? 

[cesarvinas]

Thank you, @Greg_Deckler. Using that function did the trick. I wish Power BI didn't alter time zones during data load though. In my case where I'm loading data from my lake that has been already curated, the extra labor of reconverting to UTC doesn't make sense.

The next challenge is to convert the UTC values into EST with DST. The reason it has to be done in this way, as opposed to using the automatic LocalTime conversion, is that the PBI project can be used by people in different time zones or when hosted in PBI Service, the local time zone there will be different. I need to hardcode EST/DST values instead as a separate column. From my research it seems that the only way is to have either a function that calculates the UTC offsets (-5 for EST and -4 for EDT) based on a date parameter and then use the calculated offset with the DateTimeZone.SwitchZone function. Do you happen to know of another way? 

[Syndicate_Admin]

Hello, I have a similar problem, what function did you use or finally what was the method for PBI to respect the time zone with which you uploaded your data?.

Greetings, thank you for your help.