Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 share me in Power Query how to convert this Date column from UTC to EST time zone
Here is the sample data looks like -
Thanks,
Santosh
Solved! Go to Solution.
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!
@Syndicate_Admin , I am looking for the same solution that auto process Day time saving as well.
So far this is semi-manual way I found
Add a Refresh Time Stamp To Your Power BI Reports — ehansalytics
I'm using the powerbi template - CQ and AA analytics combined analytics. The call queue analysis table has time in UTC. How do I add a column, conversion to conver this to EST? When filtering on the measure Calls volumne for a day, the time is in UTC which is not helpful for our staff when they are looking at what time calls are coming in.
I'm very new to powerbi and would like some assistance how I can add a column to this table to convert the Date column to a Date column in EST.
I've tried to follow these suggestions but not sure how to apply this to the template.
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!
Hi,
how does the function take care when the day light savings kicks in ? Is there a function that looks at the date and decides whether it should be -5 or -4 (Eastern Daylight Time) ?
Thanks
Jaco
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
66 | |
27 | |
18 | |
12 |