Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
Will appreciate some help to do this. Thx
Solved! Go to 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
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
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:
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: _
done! Thanks again!
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |