Forum Discussion

sanjyot_firke's avatar
sanjyot_firke
Helper II
3 years ago
Solved

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

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 t...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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's avatar
v-yueyunzh-msft
Community Support
3 years ago

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