Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Converting Date/Time to Local Time considering Daylight savings

I am importing a SharePoint list which saves the Datetime in Pacific Time (US). I now have the task of converting into Indian Standard Time (+05:30 GMT) but I need to take Daylight savings time into consideration. I have been trying with DateTimeZone functions but have not been successful.

 

The SharePoint list does not add the Timezone next to the timestamp in my column DueDateTime. Any suggestion is greatly appreciated.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    You can try to use custom column to show the formatted date/timezone text.


    Steps:

    1. Open query editor.
    2. Add custom column to display the date value.
    3. Change this column to date/time/timezone type.
    4. Change the column's type to text.
    5. Close and return to report view.

     

    Query:


     Custom = Table.TransformColumnTypes(Table.TransformColumnTypes(Table.AddColumn(#"Changed Type", "Custom", each [Date]),{{"Custom", type datetimezone}}),{{"Custom", type text}})

     

     

     

    Regards,

    Xiaoxin Sheng

     

     

3 Replies

  • powerbiphil's avatar
    powerbiphil
    New Member
    9 years ago

    Try this:

     

    1. in the Query Editor, select your original column.
    2. In the Transform tab, change the datatype to Date/Time/Timezone.
    3. Use the Add Column tab to make a new column... one way is to use the "Date" dropdown menu and choose "Date Only" and replace the functions it creates with the DateTimeZone functions that you want.
      • With the original column selected, in the Add Column tab choose "Custom Column" and in the popup window enter a formula such as "=DateTimeZone.ToLocal([youroriginaldatecolumn])"

     

    This worked for me with data from Dynamics CRM that came in as UTC time, but it only worked in the query editor - after I hit "Close & Apply" I got errors in every row - that's actually why I came to the forum so I'll be posting a new question about that that shortly, but maybe it will work for you.

     

    EDIT:

     

    It actually worked when I just used "custom column" in the Add Column tab instead of starting with the "date>>date only" gadget.  This correction is reflected above.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    You can try to use custom column to show the formatted date/timezone text.


    Steps:

    1. Open query editor.
    2. Add custom column to display the date value.
    3. Change this column to date/time/timezone type.
    4. Change the column's type to text.
    5. Close and return to report view.

     

    Query:


     Custom = Table.TransformColumnTypes(Table.TransformColumnTypes(Table.AddColumn(#"Changed Type", "Custom", each [Date]),{{"Custom", type datetimezone}}),{{"Custom", type text}})

     

     

     

    Regards,

    Xiaoxin Sheng

     

     

    • Zunair's avatar
      Zunair
      Icon for Advocate II rankAdvocate II
      6 years ago

      Well I wrote a whole post.. got an error posting it.. lol so here is a short version...  It is sad how Microsoft has websites with such issues.

       

      timezone list:

       

       

      let
    Source = Text.Split(Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/America/New_York"))[utc_offset], ":")
in
    Source

       

       

       

      Sample Table:

       

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzIAIkNLBQsrI2MrU0OFAF8FbQMDKwMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Orinal UTC" = _t]),
    #"Added OffsetHour" = Table.AddColumn(Source, "OffsetHour", each Number.From(Duration.From(timezone{0}))),
    #"Added OfsetMinutes" = Table.AddColumn(#"Added OffsetHour", "OffsetMinutes", each Number.From(Duration.From(timezone{1}))),
    #"Added Proper Time" = Table.AddColumn(#"Added OfsetMinutes", "Proper Time", each DateTimeZone.SwitchZone(DateTimeZone.FromText([Orinal UTC]), [OffsetHour], [OffsetMinutes]))
in
    #"Added Proper Time"

       

       

       

       

      edit:

      Get your timezone name from here http://worldtimeapi.org/api/timezone

      And just hope this website doesn't go down lol. I guess we can use google or maybe a local sql server that returns a date from system - and im not writing again (kinda #!@ at this forum)