Miguel Escobar Published October 21, 2019 
What time is it right now for you? We might share the same time zone, but that is usually not the case with worldwide operations.
If I say, let’s meet tomorrow at 8am. Will that be your 8am? Or will that be my 8am?
I feel like I should’ve posted this blog post a long time ago, but it’s better later than never. (maybe it was a time zone difference situaton? 🙂 )
In Power BI you can have date or date+time fields/columns once they’re loaded into your Data Model, but prior to loading them (inside the Power Query Editor) you can actually have them as date timezone, which is a specific data type that only holds date and time information, but also the time zone.
Let’s have a quick scenario. Ken is in Canada, Miguel is in Panama – let’s define a set of appointments specific for both time zones.
I’ve been working with Ken Puls for quite a few years now and he’s usually around 2 hours behind me, so the systems that we use are under a specific timezone for some and in a different timezone for others, which we need to “switch” to a unified timezone.
Datetimezone data type in Power BI / Power Query
I have these dates that Ken sent my way specific for a set of orders that came into our system. I’m trying to align all of my dates in my model to be under my current timezone in Panama, but Ken sent me these in his own timezone:
How do I transform these datetimezone values into my local time zone?
Easies way to transform to local time zone in Power BI / Power Query
The easies way to accomplish this is to actually go into either the Transform tab or the Add Column tab and go into the Date & Time Group.
Once in that group, select the option for time and you’ll see from the dropdown a choice for local time
And the result of that operation will yield something like this.
(note that I used the option from the Add Column tab)
Notice one importing aspect about this approach from the formula bar and that is that it uses the DateTimeZone.ToLocal function which only requires a value with the datetimezone data type.
The caveat here is that it uses the local time from the regional settings of my machine, meaning that if I was to use a machine that had different regional settings, it wouldn’t yield the correct result.
This begs the question; how do I explicitly tell Power Query to always convert the value to a specific time zone?
Recommended way to convert time zones in Power BI / Power Query
This one requires you to use a specific function, but it’s quite a simple function called DateTimeZone.SwitchZone which, in comparison to the DateTimeZone.ToLocal, only adds a second argument where you can input (as a number) the correct time zone to which you want to “switch” your original datetimezone value.
The result of that will look like this:
General Suggestions
When creating a Data Model with Power BI, it is recommended that your dates are on the same time zone, so that you don’t have to deal with multiple timelines which could get messy, complicated and yield not intuitive results in the end.
The best way to accomplish this is to do a full assessment of your data sources and make sure that all of them have the same time zone. If this isn’t the case and you can’t change them to be on the same time zone, then you can “switch” the time zone once it lands on the Power Query window.
One SUPER important thing to take in consideration is that if you plan to publish this query to the Power BI Service, the actual tenant itself could be on a different time zone than the one that you’re in, so it is recommended that you use DateTimeZone.SwitchZone.
Once your data lands on the Power BI Data Model, the time zone data is completely stripped off the value and what you see on the Data view is just a date or date time:
So it is extremely important that you make sure that everything is working correctly in Power Query as that’s the only way to tell if the time zone is correct or not.
What if my data is not set as a datetimezone?
This is often the case with simple data sources. These simple data sources do not provide more information other than the data as either date or date+time. What can you do in these cases?
The first thing is to do is to make sure that you’re dealing with at least a datetime data type. Once you have your values as datetime, you can create a new column using the following formula:
The formula that does the magic is DateTime.AddZone which adds a time zone to your datetime value. You pass your datetime as your first argument and set the time zone as your second argument.
Once you set your value as a datetimezone data type, you can go ahead and “switch” the timezone to whatever timezone using the methods described previously in this article.
Conclusion
Having the correct date and time is EXTREMELY important.
Let me know what you think about these methods! Do you have any scenarios that are closely related to time zones but not covered in this post? Let me know in the comments below.
Is there a way to use named time zones to adjust for daylight savings – like the SQL AT TIME ZONE? https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql
Thats a good question! Let me do some digging
Same question here: Our database stores datetimes in UTC only and the DateTimeZone.SwitchZone function should do the conversion to local time, including DST adjustments. For just adding or subtracting one constant hour you don’t need a separate function?
There’s no function that handles DST in Power Query, but if you want to add a specific hour, minute or second you can always add or subtract by using a duration:
https://docs.microsoft.com/en-us/powerquery-m/duration-hours
I was having an issue with data saved in our SQL Database in UTC time. We were trying to determine when scheduled tasks were running most frequently, but the chart was obviously wrong from what we knew about the data:
Please Help me
Hey! You’ll need to look at your tenant to determine the time being used at the server level. Then you can use the technique described here to offset the data accordingly.
Hi Miguel
Couldn’t agree more to ‘having the correct date and time is EXTREMELY important’.
I like to share an advanced approach to your last section ‘What if my data is not set as a datetimezone’ which also deals with the daylight saving issue. I ran across this problem when importing CSV files from a SharePoint server running on Pacific Time. I’m using the SharePoint.Files() connector in PBI Dataflows to return file name, date modified and content. Unfortunately the Date Modified does not include the datetimezone, so it may be PST (UTC-7) or PDT (UTC-8) depending on the date.
I solved this adding a helper table (DaylightSavingsUS) which calculates the DST periods by year (2nd Sunday in March to 1st Sunday in November). To determine the correct start/finish dates, I adopted an older Excel solution described here: http://excelplaza.com/ep_houdini/article_001_variable_holidays/houdini_001_variable_holidays.php
With this DST helper table it is very easy to determine if a given datetime value falls into daylight saving period or not (see also sample query below):
1. Given is a table with date stamps in Pacific time formatted as datetime (i.e. missing timezone resp. UTC offset)
2. First get the year of the date stamp
3. Merge the DST helper table to return DST Start and End datetimes for the given year
4. Determine the DST offset (0: standard (winter) time, 1 = daylight saving (summer) time)
[DST] = if [Date modified] = [DST End Time] then 0 else 1
5. Convert date stamp to proper datetimezone (here pacific standard = UTC-7 and subtract DST offset, i.e. becomes UTC-8 in summer)
[Date Modified – Pacific Time] = DateTime.AddZone([Date modified],-7 -[DST])
6. Convert to UTC datetime as well (better for dataflows which don’t store datetimezone, but just datetime)
[Date Modified – UTC] = DateTimeZone.ToUtc([Date Modified PT])
I’m also enclosing a DaylightSavingsEU helper table, which uses a slightly different logic switching on the LAST Sunday in March and the LAST Sunday in October. Though, EU may discontinue switching to daylight saving soon.
Regards,
Michael
===================================
Sample conversion for Pacific Time:
===================================
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“lY9BCoAgEEWvIq0D549aOrsOELQX73+NjMoyEgpm9/jv/4mxA2umfAjKCUYhUtPcpb4iXuD/Evs14zXcCSy1wNAArppMhSCbQt2PsCMWuI0sB4FmlBBM6x0277q8bLzrbhtYCHXTFWL70A1FV35NKw==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#”Date modified” = _t]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date modified”, type datetime}}),
#”Get Year” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date modified]), Int64.Type),
#”Merge DST-US” = Table.NestedJoin(#”Get Year”, {“Year”}, DaylightSavingsUS, {“Year”}, “DaylightSavingsUS”, JoinKind.LeftOuter),
#”Expanded DaylightSavingsUS” = Table.ExpandTableColumn(#”Merge DST-US”, “DaylightSavingsUS”, {“DST Start Time”, “DST End Time”}, {“DST Start Time”, “DST End Time”}),
// 0 = for Standard time (Winter)
// 1 = for Daylight Saving (Summer)
//
#”Determine DST offset” = Table.AddColumn(#”Expanded DaylightSavingsUS”, “DST”,
each if [Date modified] = [DST End Time] then 0
else 1),
// Winter : Pacific Standard Time (PST) = UTC – 7
// Summer: Pacific Daylight Time (PDT) = UTC – 8
#”Date Modified – Pacific Time” = Table.AddColumn(#”Determine DST offset”, “Date Modified PT”, each DateTime.AddZone([Date modified],-7 -[DST])),
#”Date Modified – UTC” = Table.AddColumn(#”Date Modified – Pacific Time”, “Date Modified UTC”, each DateTimeZone.ToUtc([Date Modified PT])),
#”Removed Other Columns” = Table.SelectColumns(#”Date Modified – UTC”,{“Date modified”, “Date Modified PT”, “Date Modified UTC”})
in
#”Removed Other Columns”
==================
DaylightSavingsUS:
==================
let
//Determines daylight saving periods in US for 2007 or later to facilate UTC conversions.
// Example
//PST = UTC – 8 (Winter)
//PDT = UTC – 7 (Summer)
Source = {2007..2040},
#”### SETTINGS ###” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”US changes DST ever year” = Table.RenameColumns(#”### SETTINGS ###”,{{“Column1”, “Year”}}),
#”.. at 02:00 am” = Table.AddColumn(#”US changes DST ever year”, “DST Time”, each #time(2,0,0)),
#”.. on Sundays,” = Table.AddColumn(#”.. at 02:00 am”, “Weekday”, each Day.Sunday),
#”.. starting in March” = Table.AddColumn(#”.. on Sundays,”, “Start Month”, each 3),
#”.. on the 2nd Sunday,” = Table.AddColumn(#”.. starting in March”, “Start Day”, each 2),
#”.. ending in November” = Table.AddColumn(#”.. on the 2nd Sunday,”, “End Month”, each 11),
#”.. on the 1st Sunday.” = Table.AddColumn(#”.. ending in November”, “End Day”, each 1),
#”### CALCULATE ###” = #”.. on the 1st Sunday.”,
// inspired by http://excelplaza.com/ep_houdini/article_001_variable_holidays/houdini_001_variable_holidays.php
#”DST Start Date” = Table.AddColumn(#”### CALCULATE ###”, “DST Start Date”,
each Date.AddDays (#date([Year],[Start Month],7*[Start Day]),
– Date.DayOfWeek(
#date([Year],[Start Month],7 – [Weekday]))
)),
#”DST End Date” = Table.AddColumn(#”DST Start Date”, “DST End Date”, each Date.AddDays (#date([Year],[End Month],7*[End Day]),
– Date.DayOfWeek(
#date([Year],[End Month],7 – [Weekday]))
)),
#”Select Columns” = Table.SelectColumns(#”DST End Date”,{“Year”, “DST Time”, “DST Start Date”, “DST End Date”}),
#”Changed Type” = Table.TransformColumnTypes(#”Select Columns”,{{“Year”, Int64.Type}, {“DST Start Date”, type date}, {“DST End Date”, type date}, {“DST Time”, type time}}),
#”DST Start Date & Time” = Table.AddColumn(#”Changed Type”, “DST Start Time”, each [DST Start Date] & [DST Time], type datetime),
#”DST End Date & Time” = Table.AddColumn(#”DST Start Date & Time”, “DST End Time”, each [DST End Date] & [DST Time], type datetime),
#”### RESULT ###” = Table.SelectColumns(#”DST End Date & Time”,{“Year”, “DST Start Time”, “DST End Time”})
in
#”### RESULT ###”
==================
DaylightSavingsEU:
==================
let
//Determines daylight saving periods in EU for 2001 or later to facilate UTC conversions.
//Watch out for announcement to end change to daylight saving in EU by 2021
// Example
//CET = UTC + 1 (Winter)
//CEST = UTC + 2 (Summer)
Source = {2001..2021},
#”### SETTINGS ###” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”EU changes DST ever year” = Table.RenameColumns(#”### SETTINGS ###”,{{“Column1”, “Year”}}),
#”.. at 01:00 am” = Table.AddColumn(#”EU changes DST ever year”, “DST Time”, each #time(1,0,0)),
#”.. on the last Sunday” = Table.AddColumn(#”.. at 01:00 am”, “Weekday”, each Day.Sunday),
#”.. starting in March” = Table.AddColumn(#”.. on the last Sunday”, “Start Month”, each 3),
#”.. ending in October.” = Table.AddColumn(#”.. starting in March”, “End Month”, each 10),
#”### CALCULATE ###” = #”.. ending in October.”,
// inspired by http://excelplaza.com/ep_houdini/article_001_variable_holidays/houdini_001_variable_holidays.php
#”DST Start Date” = Table.AddColumn(#”### CALCULATE ###”, “DST Start Date”, each Date.AddDays (
Date.EndOfMonth(#date([Year],[Start Month],1)),
– Date.DayOfWeek(
Date.EndOfMonth(#date([Year],[Start Month],1)),
[Weekday])
)),
#”DST End Date” = Table.AddColumn(#”DST Start Date”, “DST End Date”, each Date.AddDays (
Date.EndOfMonth(#date([Year],[End Month],1)),
– Date.DayOfWeek(
Date.EndOfMonth(#date([Year],[End Month],1)),
[Weekday])
)),
#”Select Columns” = Table.SelectColumns(#”DST End Date”,{“Year”, “DST Time”, “DST Start Date”, “DST End Date”}),
#”Changed Type” = Table.TransformColumnTypes(#”Select Columns”,{{“Year”, Int64.Type}, {“DST Start Date”, type date}, {“DST End Date”, type date}, {“DST Time”, type time}}),
#”DST Start Date & Time” = Table.AddColumn(#”Changed Type”, “DST Start Time”, each [DST Start Date] & [DST Time], type datetime),
#”DST End Date & Time” = Table.AddColumn(#”DST Start Date & Time”, “DST End Time”, each [DST End Date] & [DST Time], type datetime),
#”### RESULT ###” = Table.SelectColumns(#”DST End Date & Time”,{“Year”, “DST Start Time”, “DST End Time”})
in
#”### RESULT ###”
Hi Michael,
could you please provide pbix for better understanding with above queries?
Thanks,
Kailas S
Hi Kailas,
I can only post here, but not upload files. But you can create the PBIX easily by yourself. Simply open a new PBIX and enter Power Query. Then create three queries and copy each code segment above using advanced editor.
The first query can be called anything, it’s prefilled with some sample data to illustrate how the conversion works.
The US-DST query should be called ‘DaylightSavingsUS’ to match the table-merge statement in this row of the first query:
#”Merge DST-US” = Table.NestedJoin(#”Get Year”, {“Year”}, DaylightSavingsUS, {“Year”}, “DaylightSavingsUS”, JoinKind.LeftOuter),
The third query was not used in my example, but I called it ‘DaylightsSavingsEU’. To make use of it, you would obviously adjust the table-merge statement above.
Regards,
Michael
Hello Michael Marx
Thanks for the nice scripts which work fine.
Your sample conversion for Pacific Time bases on a table from binary.
Is it possible to turn the script into a function which could be used in any table with one or multiple datetime columns ?
Thanks a lot for your feedback
Best regards
Joerg
===================================
Sample conversion for Pacific Time:
===================================
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“lY9BCoAgEEWvIq0D549aOrsOELQX73+NjMoyEgpm9/jv/4mxA2umfAjKCUYhUtPcpb4iXuD/Evs14zXcCSy1wNAArppMhSCbQt2PsCMWuI0sB4FmlBBM6x0277q8bLzrbhtYCHXTFWL70A1FV35NKw==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#”Date modified” = _t]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date modified”, type datetime}}),
#”Get Year” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date modified]), Int64.Type),
#”Merge DST-US” = Table.NestedJoin(#”Get Year”, {“Year”}, DaylightSavingsUS, {“Year”}, “DaylightSavingsUS”, JoinKind.LeftOuter),
#”Expanded DaylightSavingsUS” = Table.ExpandTableColumn(#”Merge DST-US”, “DaylightSavingsUS”, {“DST Start Time”, “DST End Time”}, {“DST Start Time”, “DST End Time”}),
// 0 = for Standard time (Winter)
// 1 = for Daylight Saving (Summer)
//
#”Determine DST offset” = Table.AddColumn(#”Expanded DaylightSavingsUS”, “DST”,
each if [Date modified] = [DST End Time] then 0
else 1),
// Winter : Pacific Standard Time (PST) = UTC – 7
// Summer: Pacific Daylight Time (PDT) = UTC – 8
#”Date Modified – Pacific Time” = Table.AddColumn(#”Determine DST offset”, “Date Modified PT”, each DateTime.AddZone([Date modified],-7 -[DST])),
#”Date Modified – UTC” = Table.AddColumn(#”Date Modified – Pacific Time”, “Date Modified UTC”, each DateTimeZone.ToUtc([Date Modified PT])),
#”Removed Other Columns” = Table.SelectColumns(#”Date Modified – UTC”,{“Date modified”, “Date Modified PT”, “Date Modified UTC”})
in
#”Removed Other Columns”
It seems the function AddZone only works if the data is imported. When I use that on a direct query, i get the message the query containes transformations that cant be used for directquery. Is there a solution for direct query to adjust timezone?
Probably a dax based one to add / subtract hours from a datetime
In order to show the last processed date of a cube in Power BI I have created a column with the code DateTimeZone.LocalNow()
However this gives the timestamp of west europe where Analysis Services is hosted, and not the time zone of my users.
What is the best way to dynamically change this to the correct time? We have summer time, so I need to handle that as well.
The value itself is loaded into your data model. This value can’t be changed, so I’m not sure how you can show a dynamic date time value that will adjust based on where your user is located from one single value.
You could have a table with multiple date time values in different time zones and then have the user select what time zone they are in for them to see it in their own time zone.
I could be wrong and there’s a quick an east way to accomplish what you’re looking for. If you find it, please let me know
Oh well, that is true, and I did not mean for it to be dynamic based on the users. I know which time zone I want it to be in for all users, but since we have summer time I cannot always add one hour to DateTimeZone.LocalNow(). I would like to convert it to our time zone so that it dynamically will add one or two hours.
You’ll need to create a custom function for it and provide that custom logic in that function or perhaps have the custom function grab the values from an external table. There’s no native M function that will do this out of the box
Hello Michael
Your scripts work brilliant for me.
How could I bring below part into a function, because I have a few tables with each several datetime columns which need to be converted, and it is a mess to script this part for all the columns in each script:
#”Get Year” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date modified]), Int64.Type),
#”Merge DST-US” = Table.NestedJoin(#”Get Year”, {“Year”}, DaylightSavingsUS, {“Year”}, “DaylightSavingsUS”, JoinKind.LeftOuter),
#”Expanded DaylightSavingsUS” = Table.ExpandTableColumn(#”Merge DST-US”, “DaylightSavingsUS”, {“DST Start Time”, “DST End Time”}, {“DST Start Time”, “DST End Time”}),
// 0 = for Standard time (Winter)
// 1 = for Daylight Saving (Summer)
//
#”Determine DST offset” = Table.AddColumn(#”Expanded DaylightSavingsUS”, “DST”,
each if [Date modified] = [DST End Time] then 0
else 1),
// Winter : Pacific Standard Time (PST) = UTC – 7
// Summer: Pacific Daylight Time (PDT) = UTC – 8
#”Date Modified – Pacific Time” = Table.AddColumn(#”Determine DST offset”, “Date Modified PT”, each DateTime.AddZone([Date modified],-7 -[DST])),
#”Date Modified – UTC” = Table.AddColumn(#”Date Modified – Pacific Time”, “Date Modified UTC”, each DateTimeZone.ToUtc([Date Modified PT])),
#”Removed Other Columns” = Table.SelectColumns(#”Date Modified – UTC”,{“Date modified”, “Date Modified PT”, “Date Modified UTC”})
in
#”Removed Other Columns”
Thanks for your feedback !
Best regards
Joerg
Hey! I’m not entirely sure of what I’m looking at. I’d recommend that you post your full scenario in a forum such as the official power query forum:
https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery
Hello Miguel
My question was for Michael Marx regarding the script he posted.
I will reply on his post.
Best regards
Joerg
Hi Joerg,
sorry for delayed response. I agree, it’s a great improvement idea to turn the DST conversion logic into a function. Though, actually I never worked with functions in PBI, so maybe some else may be able to support here. I just had to solve it for a single (Pacific) datetime field (i.e. the file stamp (Date modified) of imported CSV files from SharePoint).
But the math should work the same within a function, as the complexity part within the two helper queries DST-US & DST-EU should just stay as is. In fact those are ‘static’ tables, even though I calculate them on the fly. The function just needs to check whether a given timestamp finds a match within the DST-US (or EU) table.
For the function parameters think about ‘Date Modified’ in the first query as the ‘input’ timestamp for the function, whereas “Date Modified UTC” is the function result. then you would also need a function parameter to select EU-DST vs. US-DST, and another for the timezone of the ‘input’ time stamp (hardcoded as -7 for PST in my example).
So in pseudo language, the function should best be called as:
ConvertTimestampToUTC (input Timestamp as datetime, input DSTzone as string, input Timezone as integer, output TimestampUTC as datetimezone).
example:
ConvertToUTC (#datetime(2020, 8, 31, 17, 0, 0), “US” , -7, varDateTimeUTC)
Or alternatively create two dedicated functions and omit the EU/US parameter, e.g.
ConvertUSdatetimeToUTC (#datetime(2020, 8, 31, 17, 59, 0), -7, varDateTimeUTC)
ConvertEUdatetimeToUTC (#datetime(2020, 8, 31, 17, 59, 0), +1, varDateTimeUTC)
Optionally you may even omit the Timezone parameter in case the input timestamp is already formatted as datetimezone, e.g.
ConvertUSdatetimezoneToUTC (#datetimezone(2020, 8, 31, 17, 59, 0, -7, 0), varDateTimeUTC)
But then the logic needs to be adjusted to replace:
“each DateTime.AddZone([Date modified],-7 -[DST]))”
with
“each DateTime.AddZone([Date modified], -[DST]))”
… just guessing here, better to be tested.
Regards,
Michael
Hi All,
This blog has been super helpful, Thank you!
Here is my before and after….
Before
= Table.AddColumn(#”Changed Type”, “LTZ Modified”, each DateTimeZone.ToLocal([Modified]), type datetimezone)
After
= Table.AddColumn(#”Changed Type”, “LTZ Modified”, each DateTimeZone.SwitchZone([Modified],-6), type datetimezone)
My Bi report has about 30 of these fields that will have this timezone adjustment.
Is there any way to have the -6 of the timezone be a variable?
This way when DST switches to CST I would only need to change one value.
I see from many places that taking DST into account is combersome. So OK if we have to force the published report to show -6 fine. But if the value of -6 can be obtained from another table that only has one record and with the value of -6. Then updating the report would be less of a burden down the road. Thanks again for any help.
Hey!
You can create a parameter and reuse that parameter in multiple queries. Here’s an article that I wrote about this topic:
https://www.thepoweruser.com/2019/02/12/parameters-and-functions-power-bi-power-query/
Hi all,
I was looking into this issue as a database I am using has all the datetime’s in UTC and I am located in EST. I came across a function that someone had posted on the power bi forum at https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-savings/m-p/790149#M26463, and then also watched a YouTube video at https://www.youtube.com/watch?v=0f4-R3jQWRA.
For my purposes, I actually merged what the person did in the YouTube video and created a function from it. I am leaving this function named EST, though more likely it is more like a UTC to Local time function. it basically takes your datetime field, adds a timezone making it UTC, then converts from UTC to the local time zone of your computer, and then finally removes the timezone altogether. I made the function to allow nullable values as some of the columns I am converting are null as they were never used.
So to use this, just add a column with the Custom Invoke Function, and you have a new column converted from UTC to Local Time. Mind you if the time you are getting is not UTC, you will still have to tweak this a little bit.
Hope this is helpful.
Jeff
From the power bi forum:
—————————————————————————————–
(datetimecolumn as datetime) =>
let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),
isSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0))
or
(date > SecondSundayOfMarch and date = #time(1,0,0)),
timeZone = (5 – Number.From(isSummerTime))*-1,
EST =
DateTime.From(date)
+ #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
+ #duration(0, timeZone, 0, 0)
in
EST
The M Query logic generated by following the YouTube video, for which I named the column to convert with the name of [DateTimetoChange] for which you would replace with the column name you are working with:
—————————————————————————————–
#”Added Custom” = Table.AddColumn(unavailabilityType_Table, “Custom”, each DateTime.AddZone( [DateTimetoChange], 0 )),
#”Calculated Local Time” = Table.TransformColumns(#”Added Custom”,{{“Custom”, DateTimeZone.ToLocal, type datetimezone}}),
My simplified function made from the logic shown in the YouTube video which I am showing above. I have broken the steps down for each reading, and below I have commented out a version where it does it all in one formula.
—————————————————————————————–
(datetimecolumn as nullable datetime) =>
let
DateTimeAddZone = DateTime.AddZone( datetimecolumn, 0 ),
DateTimetoLocal = DateTimeZone.ToLocal( DateTimeAddZone ),
DateTimeRemoveZone = DateTimeZone.RemoveZone( DateTimetoLocal ),
UTC_To_Local = DateTimeRemoveZone
// UTC_To_Local = DateTimeZone.RemoveZone( DateTimeZone.ToLocal(DateTime.AddZone( datetimecolumn, 0 )))
in
UTC_To_Local
Sorry, I am unsure why the formatting looks so screwy. Let me try pasting it again, as it doesn’t let me edit. I have manually deleted and reentered all line returns in this edit box, so hopefully it will take the formatting which certain will make it easier to read.
——————————————————————————–
Hi all,
I was looking into this issue as a database I am using has all the datetime’s in UTC and I am located in EST. I came across a function that someone had posted on the power bi forum at https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-savings/m-p/790149#M26463, and then also watched a YouTube video at https://www.youtube.com/watch?v=0f4-R3jQWRA.
For my purposes, I actually merged what the person did in the YouTube video and created a function from it. I am leaving this function named EST, though more likely it is more like a UTC to Local time function. it basically takes your datetime field, adds a timezone making it UTC, then converts from UTC to the local time zone of your computer, and then finally removes the timezone altogether. I made the function to allow nullable values as some of the columns I am converting are null as they were never used.
So to use this, just add a column with the Custom Invoke Function, and you have a new column converted from UTC to Local Time. Mind you if the time you are getting is not UTC, you will still have to tweak this a little bit.
Hope this is helpful.
Jeff
From the power bi forum:
—————————————————————————————–
(datetimecolumn as datetime) =>
let
date = DateTime.Date(datetimecolumn),
time = DateTime.Time(datetimecolumn),
firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),
isSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0))
or
(date > SecondSundayOfMarch and date = #time(1,0,0)),
timeZone = (5 – Number.From(isSummerTime))*-1,
EST =
DateTime.From(date)
+ #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))
+ #duration(0, timeZone, 0, 0)
in
EST
The M Query logic generated by following the YouTube video, for which I named the column to convert with the name of [DateTimetoChange] for which you would replace with the column name you are working with:
—————————————————————————————–
#”Added Custom” = Table.AddColumn(unavailabilityType_Table, “Custom”, each DateTime.AddZone( [DateTimetoChange], 0 )),
#”Calculated Local Time” = Table.TransformColumns(#”Added Custom”,{{“Custom”, DateTimeZone.ToLocal, type datetimezone}}),
My simplified function made from the logic shown in the YouTube video which I am showing above. I have broken the steps down for each reading, and below I have commented out a version where it does it all in one formula.
—————————————————————————————–
(datetimecolumn as nullable datetime) =>
let
DateTimeAddZone = DateTime.AddZone( datetimecolumn, 0 ),
DateTimetoLocal = DateTimeZone.ToLocal( DateTimeAddZone ),
DateTimeRemoveZone = DateTimeZone.RemoveZone( DateTimetoLocal ),
UTC_To_Local = DateTimeRemoveZone
// UTC_To_Local = DateTimeZone.RemoveZone( DateTimeZone.ToLocal(DateTime.AddZone( datetimecolumn, 0 )))
in
UTC_To_Local
Why the heck Crimea and Donbas are Russia? Where did you get this map?
No clue. Was the first map that came up in a google search for time zones back in the day.
Excellent solution. It worked for me. Miguel, many thanks for sharing.