Hi there,
I am trying to convert UTC DateTime column into NZ standard time using following calculation in Power query:
DateTimeZone.SwitchZone([Outage Planned Start],12,0)
When I execute the command, it throws the following error and when I clicked on the error, it shows the following screen:
I am not sure how to fix the issue. Could anyone help me to fix the issue?
Hi Leo,
I do not know about the function DateTimeZone.SwitchZone, but I needed a similar solution in the past and the following steps should solve the problem in the query editor.
The custom action is found on this link:
https://community.powerbi.com/t5/Power-Query/Convert-Date-Time-in-UTC-to-Local-Time-with-Daylight-savings/m-p/1086128
(all credits of course go to the author of this post!)
Note that for New Zealand you should change the function to substract the 12 hours:
timeZone = (-12 - Number.From(isSummerTime))-1*
Happy to assist if you have further questions.
@Rens Thanks for the solution. I have four date columns that I wanted to convert into NZST. Does that mean that I have to create four different functions (one for each datetime column) and then create four new columns that convert datetime to NZST using those queries. Am I right?
I had a similar issue where I needed to convert the datetime columns in a fact table to WA time but only for that table. I created a new column referencing the original datetime columns in the table using this statement in Power Query
Table.AddColumn(factTable, “Datetime Column Fixed”, each DateTimeZone.RemoveZone(DateTimeZone.ToLocal (DateTime.AddZone([Datetime Column],3))))
SIne AET is offset 3 hours from WA time, the valiue to offset in the statement is 3. If your Date dimension is not corrected from UTC, you would need to set that offset to the NZ offset from UTC.
Hope that make sense.
DateTimeZone.SwitchZone is what I use in my Date dimension to correct dates to local timezone from UTC in the PBI service. The following example was taken from Radacad’s series in creating a date dimension table.
/ configurations start
StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
DayTime = DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),10,0),
Today=Date.From(DayTime), // today’s date
firstDayofWeek=Day.Monday, // set the week’s start day, values: Day.Monday, Day, Sunday…
FromDate=#date(StartYear,1,1),
ToDate=#date(EndYear,12,31),
Source=List.Dates(
FromDate,
Duration.Days(ToDate-FromDate)+1,
#duration(1,0,0,0)
),
Hi Leo,
You need to create (creating is basically copy and paste into blank query) the function only once.
Afterwards you indeed need to add four new columns (via the custom function - see my previous post). For every column you add you choose (1) the function and (2) the column that will be transformed.
Kind regards,
Rens
@Rens, Thanks, it worked. The only problem I am having is deal with null values in the date column. When the query got the cell that has null values, it throws an error that could not convert null into datetime type. So I thought, when the null values, come it should be replaced with a default date (i.e. 1/1/1900). So I made slight changes in the query as below:
date = if datetimecolumn <> null then DateTime.Date(datetimecolumn) else DateTime.Date(Date.FromText(“1/1/1900”))
However, it did not fix the issue. I am still getting the same error. Do you know any other way of tackling the issue?
Hi Leo,
Glad to hear that issue was solved.
Now what about the blanks?
You should NOT change the function in any way to solve that problem with the null values.
The query is used only to convert UTC time to NZ time.
To remove the null values, simply add a new step to your query. Replacing a value in a query is straightforward and covered in other posts in this forum (Home -> Replace values).
Best regards,
Rens