Forum Discussion
Helper IIIQuarter hour calculation in time dimension
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
Community ChampionAnswer 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
jujuthanks MarcelBeug
Thanks for the code - you are right, I can use the same solution for my fact table as well.
I have a slightly modifed request now ...
What if I wanted to create a time dimension table which lists unique quarter hour time slots over a 24 hour period - it seems I need a dimension table with unique values of the quarter hour time stamp to connect to my fact table.
- MarcelBeug
You can use function List.Times as basis.
Syntax:
List.Times(start as time, count as number, step as duration) as list
Wrapped in a query:
Table.FromColumns({List.Times(#time(0,0,0),24 * 4,#duration(0,0,15,0))},type table[quarter = time])- juju
Many thanks MarcelBeug !
- juju
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
- MarcelBeug
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
done! Thanks again!