Power Query - Order of hours in day - EU Summer and Winter time

[Anonymous]

Hi, I have trouble with changing time in EU from Summer to Winter time and back in Power Query.

I need get hour from datetime. It is simple because I can use "Time.Hour([From])". The problem is in two days in year when the time is changing from summer to winter and back (for example in 2019 it is day 31.3.2019 and 27.10.2019).

27.10.2019 - I get 2 similar values for time 02:00:00

31.3.2019 - I don't get value for time 02:00:00

Below is picture, where is my "goal". From each datetime I need to get hour respectively order of hours in day.

I can't use something like in sql "row_number() over (partition by date order by time)" because I do not have to have all hours of the day available.

"Because of" in picture below is table, which is my final goal, but it is second step. The first is get hours from datetime, I think.

Thank you

[Anonymous · Accepted solution]

I think, that I have it.

this is formula for return right value from datetime in my local zone.

if (DateTime.Date([From]) = Date.StartOfWeek(#date(Date.Year([From]),11,1),Day.Sunday) 
and DateTimeZone.ZoneHours([From]) = 1
) 
then Time.Hour([From])+1
else if (DateTime.Date([From]) = Date.StartOfWeek(#date(Date.Year([From]),4,1),Day.Sunday)
and DateTimeZone.ZoneHours([From]) = 2

)
then Time.Hour([From])-1
else Time.Hour([From])