Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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"
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"
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |