Quarter hour calculation in time dimension

[juju]

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(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])),
    setDataType = Table.TransformColumnTypes(minuteIndex,{{"DayTime", type time}, {"TimeKey", type text}, {"HourIndex", Int64.Type}, {"MinuteIndex", Int64.Type}})
in
    setDataType

Credit : How to include 'Time' in Date Hierarchy in Power BI

I am looking to add the following to it :

1. My fact table contains data that come in 15 minute chunks - 12:15 am, 12:30 AM etc.... I'd like to create a new column in my time dimension table above that aggregates the minute date values into 15 minute chunks as well - showing the start of the 15 minute period - just like we have the start of the hourly calculation in M - so 12:01 AM -> 12:00 AM,  12:13 AM -> 12:00 AM, 12:15 AM -> 12:15 AM etc .
2. In my fact table, sometimes, the time data doesnt come in cleanly defined quarterly buckets by the hour e.g sometimes it shows e.g. 12:32 AM, then the next 15 minute chunk will be 12:47 AM etc. I'd like to apply the same calculation for creating the start of the quarter within each hour , in my fact table as well - so I can connect to the quarter hour column in my time dimension table.

Will appreciate some help to do this. Thx

[MarcelBeug · Accepted solution]

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

[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!

[juju]

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. 

[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 !