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