Forum Discussion
Quarter hour calculation in time dimension
- 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
hello MarcelBeug
I just tried implementing the solution for question #2 and it didnt quite work. I suspect I didnt describe what I am looking to do well.
I have a column (timestamps ) that shows time in 15 minute increments, but it may or may not be exactly in the quarter hour e.g
- 12:00 am
- 12:15 am
- 12:30 am
- 12:32 am ( sometimes it goes out of sync like this, but maintains the 15 minute gaps )
- 12:47 am
- 01:02 PM
Due to the way it goes out of sync, I am looking to clean the data by showing the start of the quarter hour for each time value:
- 12:00 am -> 12:00 am
- 12:15 am - > 12:15 am
- 12:30 am -> 12:30 am
- 12:32: am -> 12:30 a.m
- 12:47 am -> 12:45 am
- 01:02 pm -> 01: 00 pm
I'd like to add this to my fact table in the advanced query editor window.
Thanks
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: _
- juju9 years ago
Helper III
done! Thanks again!