Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |