Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Convert Date Column from UTC to EST Timezone

Hi,    I'm pulling CRM dynamics data in UTC format, I see that dates showing in Powerbi is different from CRM dynamics. Now i want to convert that date column to local time zone EST.    Please sh...
Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    UTC is 5 hrs ahead of EST,so create below M query to convert the timezone column to EST time:

    EST = DateTimeZone.SwitchZone([UTC],-5)

    And you will see:

    For the sample .pbix file,pls see attached.

     

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello Anonymous 

 

you can use the function DateTimeZone.SwitchZone. Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTXMzTUMzIwMlAwMLACIwVtMKUUqwOUNsQtHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Created_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created_Date", type datetimezone}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "EST", each DateTimeZone.SwitchZone([Created_Date], 5), type datetimezone)
in
    #"Added Custom"

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy