Power BI Timezone+Daylight convertion to CEST

[DvdH]

Good morning,

I have found tons of posts about this exact issue i wish Power BI added it as a normal input.
I however have not found a propper solution which works for me.

I am trying to convert RefreshTime to CEST/CET depending on daylight savings.
Any help?
Possible helpfull links i couldn't get to work:
DateTimeZone.SwitchZone - PowerQuery M | Microsoft Learn
DateTimeZone.ToLocal - PowerQuery M | Microsoft Learn

"I added a new table along side my other data sources. It just has one record of the local time. Mark that as Date Table and the other date times follow accordingly.

let
Source = Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Australia/Adelaide")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"abbreviation", type text}, {"client_ip", type text}, {"datetime", type datetimezone}, {"day_of_week", Int64.Type}, {"day_of_year", Int64.Type}, {"dst", type logical}, {"dst_from", type datetime}, {"dst_offset", Int64.Type}, {"dst_until", type datetime}, {"raw_offset", Int64.Type}, {"timezone", type text}, {"unixtime", Int64.Type}, {"utc_datetime", type datetime}, {"utc_offset", type text}, {"week_number", Int64.Type}})
in
#"Changed Type"

"

[v-yalanwu-msft]

Hi, @DvdH ;

 For your specific needs, maybe this custom function could work:

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 < firstSundayOfNovember) 
		or 
		(date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (7 - Number.From(isSummerTime))*-1,

Solved: Convert Date/Time in UTC to Local Time with Daylig... - Microsoft Power BI Community

Solved: Re: Convert Date/Time in UTC to Local Time with Da... - Page 2 - Power Platform Community (m...

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DvdH]

Hey! Thanks for the reply. Where/How would i implement this? I checked the links before i even made this post but i just cant seem to get it to work.

[v-rzhou-msft]

Hi @DvdH ,

According to your statement, I think you want to convert the datetime in Power BI to the datetime in your local timezone. I suggest you to refer to this code. Add this code it Advance Editor in Power Query Editor. If this calculation logic does't meet your request, you can change the calculation logic based on your requirement.

(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 < firstSundayOfNovember) 
		or 
		(date = firstSundayOfNovember and time >= #time(1,0,0)),


timeZone = (7 - Number.From(isSummerTime))*-1,

MDT = 
            DateTime.From(date) 
            + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
            + #duration(0, timeZone, 0, 0)

in
    MDT

Then invoke this query with your table.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DvdH]

Sorry for the late responds. This seems to work but not for my current table.
I got a table which is of the currentime(doesn't take the daylight savings into account)

I cant seem to paste the function into here? Any help? It worked for a diff rapport of mine with manual data's.

[DvdH]

= #table(type table[#"CET"=datetime],{{DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),1))}})

code of the table btw

[DvdH]

Yeah i found like 100 links about this but i really need some support to propperly use the querys.

[DvdH]

More info i found idontknow how to convert/import into my own rapport.

//create one entry for each year that exists in the dataset
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2018
then 
    //set the start/end of daylight savings for that year.
    // (first number is the start, second is the end)
    //this is in UTC time in the format of MMDDHH
    if [MonthDayHour] >= 31109 and [MonthDayHour] < 110408 
    //set the UTC offset if daylight savings is active
    then DateTimeZone.SwitchZone([datetimeUTC],-6) 
    //set the UTC offset if daylight savings is not active
    else DateTimeZone.SwitchZone([datetimeUTC],-7)
else
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2019
then 
    if [MonthDayHour] >= 31009 and [MonthDayHour] < 110308 
    then DateTimeZone.SwitchZone([datetimeUTC],-6) 
    else DateTimeZone.SwitchZone([datetimeUTC],-7)
else
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2020
then
    if [MonthDayHour] >= 30809 and [MonthDayHour] < 110108 
    then DateTimeZone.SwitchZone([datetimeUTC],-6) 
    else DateTimeZone.SwitchZone([datetimeUTC],-7)
else 
if Record.Field(DateTimeZone.ToRecord([datetimeUTC]),"Year") = 2021
then
    if [MonthDayHour] >= 31409 and [MonthDayHour] < 110708 
    then DateTimeZone.SwitchZone([datetimeUTC],-6) 
    else DateTimeZone.SwitchZone([datetimeUTC],-7)
//set the default if no year is defined
else DateTimeZone.SwitchZone([datetimeUTC],-7)