Forum Discussion

Switto's avatar
Switto
Helper IV
4 years ago
Solved

Time zone conversion

Hi Again,

 

I am working on a list that is coming from SharePoint and has 4 different date and time columns.

It has different time zones like below.

MemberFirm
Fri Dec 03 2021 13:56:07 GMT+0800 (China Standard Time)
Mon Nov 22 2021 04:49:13 GMT-0500 (Eastern Standard Time)
Fri Nov 19 2021 13:08:01 GMT+0100 (Central European Standard Time)
Fri Nov 12 2021 15:46:18 GMT+0530 (India Standard Time)
Fri Oct 29 2021 12:35:15 GMT+0200 (Central European Summer Time)

Now I would like to change all this time zones to GMT+0530 (India Standard Time).

Is it possible to change it as per google search, I got result of people converting column of a particular Time zone not a mix of time zones.

Thanks in Advance 

S

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9BDoIwEEWvMmGlUZOZliLMVtG4QBeyIy4aaCKJFFPB82tFXBEP8N77vyiCnatha0pACQIFAUlWEeMa9lm+wBgRZptrbTWcO20r7SrI68bMg8uyCLLWwrF9ghADiyGHCZP07AqVZ1P96IyzU7Qve5qSXxljRhrK9Ckb2zl9g7R37d3o/5rvCFIcRkzxoFHyrTnYqp484NlT2YEYJwiWikkNrJie0DeNcaPk8gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source time" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "IST", each DateTimeZone.SwitchZone(
let dt = Date.FromText(Text.Range([Source time],4,11)),
    tm = Time.FromText(Text.Range([Source time],16,8)),
    ofh = Number.FromText(Text.Range([Source time],28,3)),
    ofm = Number.FromText(Text.Range([Source time],31,2))
in
#datetimezone(Date.Year(dt),Date.Month(dt),Date.Day(dt),Time.Hour(tm),Time.Minute(tm),Time.Second(tm),ofh,ofm) 
,5,30)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"IST", type datetimezone}})
in
    #"Changed Type"

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9BDoIwEEWvMmGlUZOZliLMVtG4QBeyIy4aaCKJFFPB82tFXBEP8N77vyiCnatha0pACQIFAUlWEeMa9lm+wBgRZptrbTWcO20r7SrI68bMg8uyCLLWwrF9ghADiyGHCZP07AqVZ1P96IyzU7Qve5qSXxljRhrK9Ckb2zl9g7R37d3o/5rvCFIcRkzxoFHyrTnYqp484NlT2YEYJwiWikkNrJie0DeNcaPk8gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source time" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "IST", each DateTimeZone.SwitchZone(
let dt = Date.FromText(Text.Range([Source time],4,11)),
    tm = Time.FromText(Text.Range([Source time],16,8)),
    ofh = Number.FromText(Text.Range([Source time],28,3)),
    ofm = Number.FromText(Text.Range([Source time],31,2))
in
#datetimezone(Date.Year(dt),Date.Month(dt),Date.Day(dt),Time.Hour(tm),Time.Minute(tm),Time.Second(tm),ofh,ofm) 
,5,30)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"IST", type datetimezone}})
in
    #"Changed Type"

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".