Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rodrigo_avf
Frequent Visitor

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

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

1 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.

View solution in original post

2 REPLIES 2
GilbertQ
Super User
Super User

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)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors