Forum Discussion
DATE AND HOUR TABLE
Hi All,
Could you provide some inputs on the easiet way to populate hourly rows between 2 dates.
i am looking for a table with 1 column as below.
3/27 01.00
3/27 02.00
3/27 03.00
........
3/27 24.00
3/28 01.00
3/28 02.00
Thanks
Hi revansh ,
We can add a custom column in Power Query Editor and then expand it new rows to meet your requirement:
List.DateTimes( [Start Date], Duration.TotalHours([End Date]-[Start Date]), #duration(0,1,0,0) )All queries are here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLQNdY1MlcwMLQyMAAiJEELBQMjuKChgVKsTrSSEaomY3RNxgbIJhmaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start Date" = _t, #"End Date" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start Date", type datetime}, {"End Date", type datetime}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "DateTime", each List.DateTimes( [Start Date], Duration.TotalHours([End Date]-[Start Date]), #duration(0,1,0,0) )), #"Expanded DateTime" = Table.ExpandListColumn(#"Added Custom", "DateTime") in #"Expanded DateTime"
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared?
Best regards,
3 Replies
Hi revansh ,
We can add a custom column in Power Query Editor and then expand it new rows to meet your requirement:
List.DateTimes( [Start Date], Duration.TotalHours([End Date]-[Start Date]), #duration(0,1,0,0) )All queries are here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLQNdY1MlcwMLQyMAAiJEELBQMjuKChgVKsTrSSEaomY3RNxgbIJhmaKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start Date" = _t, #"End Date" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start Date", type datetime}, {"End Date", type datetime}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "DateTime", each List.DateTimes( [Start Date], Duration.TotalHours([End Date]-[Start Date]), #duration(0,1,0,0) )), #"Expanded DateTime" = Table.ExpandListColumn(#"Added Custom", "DateTime") in #"Expanded DateTime"
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared?
Best regards,Separate date and time/hour. Create a date table and time or Hour Table
Date = [Datetime].Date
Time = [Datetime].Time //Or format([Datetime],"HH:MM:"SS") or format([Datetime],"HH")
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Hi revansh ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
