Convert Date Time in UTC to PST

[vincenardo]

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!!

[Anonymous]

@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

[Anonymous]

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

[vincenardo]

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]

[Anonymous]

At any rate, if the column was originally type datetime, you can use DateTime.AddZone([AbsentStart], -8) to convert to your time zone, or just [AbsentStart] - #duration(0,8,0,0)

--Nate

[Anonymous]

Try this:

= Table.TransformColumns(#"Resource Absence", {{"AbsentStart", DateTimeZone .ToLocal}, {"AbsentEnd", DateTimeZone.ToLocal}})

[vincenardo]

I recieved this error; ??

Expression.Error: A cyclic reference was encountered during evaluation

[Anonymous]

Ok, your last step name is #"Reordered Columns1", so:

= Table.TransformColumns(#"Reordered Columns1", {{"AbsentStart", DateTimeZone .ToLocal}, {"AbsentEnd", DateTimeZone.ToLocal}})

[vincenardo]

Appreciate the help, still have an issue;

Here is the dataset before I put in the Code you suggested (The AbsentStart_PST was using a different way to conver the AbsentStart)

I inserted the code you suggested (Custom1) - and I get this error on the AbsentStart and AbsentEnd.

Any ideas? I like your suggestion the best because the code is much less then what I used and will ensure the time is coverted to the local time.

[vincenardo]

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!

[Anonymous]

Sorry, under your applied steps, right click on the last step, click "Add new step". Then copy and paste everything including the "=" into the formula bar. 

[Anonymous]

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

[vincenardo]

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!

[PC2790]

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