Forum Discussion
Convert Date Time in UTC to PST
I have a date / time field coming in from a connection to Salesforce. The date/time is stored in UTC and I need to transform it to PST (our local time).
I tried to edit the query and 'transform' it by changing the data type to DATE/TIME/TIMEZONE. All this did was add the time zone to the field..
How do I convert this value to the current time zone??
Thanks!!
13 Replies
Hello vincenardo
Check this out-https://cloudbi.com.au/converting-utc-to-local-datetime-in-power-bi/
It might be able to address your requirement
To make the Datetime correct its value, as opposed to only adding the Time Zone, just use this one function, instead of changing the data type to datetimezone:
= Table.TransformColumns(PriorStepOrTableName, {{"AbsentStart", DateTimeZone .ToLocal}, {"AbsentEnd", DateTimeZone.ToLocal}})
This will add your local time zone to the datetime, and also offset the actual value by the difference in hours from UTC to PST.
--Nate
Can you give me some additional instructions on how to add this? The table name is ‘Resource Absence’ not sure where to add this…
Thank you!
When editing the table, can you give me the steps on where to add it? I can't figure where to place it, I assume it's not in a new column. Thanks!
I recieved this error; ??
Expression.Error: A cyclic reference was encountered during evaluation
Can you tell me what the error says, or post the M code from the advanced editor?
You could also try adding the word "each" before the DateTimeZone functions. Or, try replacing DateTimeZone.ToLocal with each DateTimeZone.SwitchZone(_, -8). Whatever you do though, you should do it once, in other words, don't change the type to DateTimeZone earlier in the query, unless you use each DateTime.AddZone([AbsentStart], -8).
--Nate
--Nate
Here is the error -
Expression.Error: We cannot convert the value #datetime(2020, 8, 18, 19, 30, 0) to type DateTimeZone.
Details:
Value=8/18/2020 7:30:00 PM
Type=[Type]
vincenardo
DateTimeZone.ToLocal() should work. This blog explains the steps in details.Handling Different Time Zones in Power BI / Power Query
What is the original data type of the screen short date columns? For the errors, Is is possible to share a pbix file including a short sample.Paul Zheng _ Community Support Team
