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(...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    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

      

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

You can use the same formula if you adjust step name "minuteIndex" to: the step name of your fact table query after which you will add the code; and field name [DayTime] to the name of your field in the fact table.

 

    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)),

 

Alternatively, if you want to transform your column instead of adding a new column, you can create base code by applying some arbitrary time function on the "Transform" tab in the query editor and then adjust the generated code to include the formula in the code above (the part after "each", excluding the last close parenthesis), replacing field name [DayTime] to an underscore: _

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

done! Thanks again!