Forum Discussion

juju's avatar
juju
Icon for Helper III rankHelper III
9 years ago
Solved

Quarter hour calculation in time dimension

  I am using the code below to create a time dimension : ( time by minute over 24 hrs )   let Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,1,0)), convertToTable = Table.FromList(...
  • MarcelBeug's avatar
    9 years ago

    Answer to question 1 in the query below.

    I don't understand question 2, especially the part "then the next 15 minute chunk will be 12:47 AM".

    But maybe you can use the answer to question 1 for question 2 as well.

     

    Your original with added step QuarterTime, also added to step setDataType. 

     

    let
        Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,1,0)),
        convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"DayTime"}, null, ExtraValues.Error),
        createTimeKey = Table.AddColumn(convertToTable, "TimeKey", each Time.ToText([DayTime], "HHmmss")),
        hourIndex = Table.AddColumn(createTimeKey, "HourIndex", each Time.Hour([DayTime])),
        minuteIndex = Table.AddColumn(hourIndex, "MinuteIndex", each Time.Minute([DayTime])),
        QuarterTime = Table.AddColumn(minuteIndex, "QuarterTime", each #time(0,0,0)+#duration(0,0,15*Number.IntegerDivide(Duration.TotalMinutes([DayTime]-#time(0,0,0)),15),0)),
        setDataType = Table.TransformColumnTypes(QuarterTime,{{"DayTime", type time}, {"TimeKey", type text}, {"HourIndex", Int64.Type}, {"MinuteIndex", Int64.Type}, {"QuarterTime", type time}})
    in
        setDataType