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

     

     

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)