Cannot convert date into Local Time zone. Option Working Incorrectly in Power Query Editor

[sanjyot_firke]

Hello Everyone,

Is anyone else facing this issue? This was fine till early this week.

The "Local Time" option working incorrectly in the dataflow power query editor. Due to this, we are unable to convert the date into Local AUS time and the data is coming as server time, which is US. It has messed up our data and totals completely.

Below snapshot of the data coming "Date - Copy" field contains date with the local time zone and Local time field contains local time zone. In both cases, the timezone should have been +10. However, it is coming as +0

How do we handle this?

Regards,

Sanjyot

[sanjyot_firke]

Hi Aniya,

Thank you so much for the detailed explanation.

How do we handle daylight savings in this function?  Can we use conditional logic?

Sydney, AUS havs daylight savings

Regards,

Sanjyot Firke

[v-yueyunzh-msft · Accepted solution]

Hi , @sanjyot_firke 

According to your descripition, in AUS havs daylight savings, and you need to judge the date if the time zone changes.

If the date is the fixable , you can use if to judge it like this (example as 6/21 per year):

if Date.Month([Column1]) >6   or  (Date.Day([Column1])>=21 and Date.Month([Column1])=6) then       DateTimeZone.SwitchZone([Column1],10) else DateTimeZone.SwitchZone([Column1],9)

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[v-yueyunzh-msft]

Hi, @sanjyot_firke 

Accoridng to your descrition, you use the "Local Time" option in Power Query to convert the timezone.

The caveat here is that it uses the local time from the regional settings of my machine, meaning that if I was to use a machine that had different regional settings, it wouldn’t yield the correct result.

This one requires you to use a specific function, but it’s quite a simple function called DateTimeZone.SwitchZone which, in comparison to the DateTimeZone.ToLocal, only adds a second argument where you can input (as a number) the correct time zone to which you want to “switch” your original datetimezone value.

The result of that will look like this:

For more information, you can refer to :
Handling Different Time Zones in Power BI / Power Query — The Power User

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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