Timezone in Power Bi Service keeps reseting to UTC, no matter what

[rodrigo_avf]

Hello,

I know there are tons of posts in the community related to time zone issues with Power BI Service, I read many of them, watched some Youtube videos, but nothing solved.

My data comes in through an API where the datetime info is stored in a column called 'time' in unix timestamp format. Following the instructions on the video below, I was able to convert it to dd/mm/yyyy hh:mm:ss format
Convert Epoch/ Unix time to datetime in Power Query in one step! - YouTube

Following the instructions from the video, my M code looks like this:
DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [time]),0))

With that I have the datetime in UTC, and no matter what I do, when I refresh the data in Power BI Service, it always returns the time in UTC.

Here is what I've tried:
1 - Use DateTimeZone.SwitchZone
DateTimeZone.SwitchZone(DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [time]),0),-3)

2 - Use DateTimeZone.SwitchZone with minutes
DateTimeZone.SwitchZone(DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [time]),0),-3,0)

3 - Use DateTimeZone.SwitchZone * 2

Thinking that maybe the API 'time' column is the issue and the returning number is different when runnning in desktop and service.
DateTimeZone.SwitchZone(DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [time]),0),-6)

4 - Change the column type to datetime using local

Adding an extra step to the query, did not work

5 - Change my desktop region time

To see if I get the same problem in Desktop as in Service, I don't, in Desktop it always works fine if I use SwitchZone

[GilbertQ]

Hi @rodrigo_avf 

Because all the Power BI Servers dates are in UTC I would then simply add the duration to your datetime column to put in  + #duration(0,-3,0,0)

[rodrigo_avf · Accepted solution]

Hi @GilbertQ 
That would probably work as well, I did not try it though, but I found the reason why it was happening and how to solve it in a fencier way.

The problem

After DateTimeZone.SwitchZone I had another step that would change the column's type to datetime only. But, it seems that whe we add the TimeZone to a datetime number it does not change the number stored, it only changes what is displayed. So although I was seeing something like 01/07/2023 08:40:32 -03:00 the value stored was still in UTC, so when I would change the column type to datetime only, the time would be in UTC.

The solution

After SwitchZone and before changing the column type to datetime, I added another step with DateTimeZone.RemoveZone which actually changes the number to the timezone of choice. After that when I change the column type to datetime the time is in the chosen timezone.