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

  • Question

  • 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

    I wanted to add pictures but "Body text cannot contain images or links until we are able to verify your account."

    Friday, January 18, 2019 9:18 AM


  • Hi PaliG,

    Because shape of your data is obscure, I tried to guess about it.

        summer = #datetime(2019, 3, 31, 2, 0, 0),
        winter = #datetime(2019, 10, 27, 2, 0, 0),
        list = List.DateTimes(Date.StartOfYear(summer),365*24,#duration(0,1,0,0)),
        table = Table.FromColumns({list},{"datetime"}),
        hours = Table.AddColumn(table, "hours", each Time.Hour([datetime]), type number),
        to_wrong = Table.ReplaceValue(hours,summer,winter,Replacer.ReplaceValue, {"datetime"}),
        duplicates = Table.Distinct(to_wrong),
        summer_table = #table(type table [datetime = datetime, hours = number], List.Zip({{summer}, {Time.Hour(summer)}})),
        combine = Table.Combine({duplicates, summer_table}),
        sort = Table.Sort(combine,{{"datetime", Order.Ascending}})

    Step to_wrong  transforms correct dataset to uncorrect (to reconstruct your case), so it may make sense for you to use subsequent steps. Hope, this way will be helpful.

    Friday, January 18, 2019 12:43 PM