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

 

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

3 Replies

  • 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

  • sanjyot_firke's avatar
    sanjyot_firke
    Helper II
    3 years ago

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