Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 consideration. I have been trying with DateTimeZone functions but have not been successful.
The SharePoint list does not add the Timezone next to the timestamp in my column DueDateTime. Any suggestion is greatly appreciated.
Solved! Go to Solution.
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
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
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)
Try this:
This worked for me with data from Dynamics CRM that came in as UTC time, but it only worked in the query editor - after I hit "Close & Apply" I got errors in every row - that's actually why I came to the forum so I'll be posting a new question about that that shortly, but maybe it will work for you.
EDIT:
It actually worked when I just used "custom column" in the Add Column tab instead of starting with the "date>>date only" gadget. This correction is reflected above.
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |