Converting UTC to local Time Zone in Power BI

Power Query
1

I’m trying to figure out how to convert UTC to my local time in powerbi Ideally through the Query Editor?

2

Hi @matthew.wright,

You will find details on how to deal with that here in this topic.
Adding a Last Refresh date to your Report - #9 by Melissa

I hope this is helpful
Let me know if you have any questions

3

Melissa,

Thanks. I looked over this but I am looking to convert dates and time in an existing table to UTC.

Right now these are all in UTC.

image

4

Okay maybe I didn’t / don’t understand the requirement correctly.

If you have a datetime column and want to change the timezone, you add a Custom Column and implement this logic, where the last number is the number of hours to offset which can be positive or negative.

DateTime.AddZone( [Created_Time], 2)

5

Melissa,

Ok. So here were my steps.

  • Made new column called Local time

  • Duplicated Created Time Column named it Time Created and converted in change type to local time.

  • Tried to add time wo seconds to Time Created with Custom Column Got An Error. Not sure why. I used the following MCode to dot hat: Time.From (Time.ToText ([Time_Created], “HH:mm”))

Any thoughts?

image

6

Hi @matthew.wright,

Extracted a small sample, try this.
To extract the time portion I’ve added: DateTime.Time( [Local Time] )

So the full code for the final step is now:
Time.From( Time.ToText( DateTime.Time( [Local Time] ), “HH:mm” ))

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcrBDcAwCATBViLeQToOWQRasei/jVixn/nO7pzCVIQSxGWj6EVK338en2N5Ho8yK+zfoHiOe3muJN0v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Created_Time = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Created_Time", type datetime}}),
    AddLocalTime = Table.AddColumn(ChangeType, "Local Time", each DateTime.AddZone( [Created_Time], -4), type datetimezone),
    AddTimewoSeconds = Table.AddColumn(AddLocalTime, "Time wo Seconds", each Time.From( Time.ToText( DateTime.Time([Local Time]), "HH:mm")), type time)
in
    AddTimewoSeconds

I hope this is helpful.

7

Hi @matthew.wright, we’ve noticed that no response has been received from you since the 16th of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

8

Hi @matthew.wright, I ask you to see the link below I think it solves your problem or can give you an idea of ​​a resolution.

9

Its still giving me problems. I’ll post some sample data. I’m hoping Its something simple I am missing

10

Thanks for posting your question @matthew.wright. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

11

@matthew.wright You can try this . m code in power query .

// convert UTC to local time defined by an offset
LocalTime = 
    if DateTimeUTC = null then
        null
    else if DateTimeUTC >= DaylightSavingTimePeriod[From] and DateTimeUTC < DaylightSavingTimePeriod[To] then
        DateTimeUTC + TimeZoneConfiguration[DaylightSavingTimeOffset]
    else
        DateTimeUTC + TimeZoneConfiguration[StandardOffset]
1 Like
12

Here are several articles I hope you’ll find useful.

https://blog.magnetismsolutions.com/blog/colinmaitland/2019/12/03/custom-power-bi-utc-to-local-date-time-with-daylight-saving-time-function

13

Hi @matthew.wright, we’ve noticed that no response has been received from you since the 21st of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

14

Hi @matthew.wright, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!