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
thanks 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.
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])- juju9 years ago
Helper III
Many thanks MarcelBeug !