Date-table: How to add 24 datetimes per day

[Anonymous]

Hi,

I have created a date-table in DAX based on following function: Date = CALENDAR(DATE(2012;1;1) ; (DATE(2020;12;31)))

I need to create a DATETIME column with all hours from 00.00 - 23.00 per date, i.e. 24 rows per date. The format should be "dd.mm.yy hh:mm:ss". Is there any formula to create such a column? If not, is there any method to create such a table in power query?

Thank you!

Example

[donsvensen · Accepted solution]

Hi

You can create it like this in Power Query

let
    Source = List.Dates(#date(2018,3,25), 1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each {0..23}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded {0}", "DateTime", each DateTime.From([Date])+#duration(0,[Custom],0,0))
in
    #"Added Custom1"

Modify the first step to create a longer period.

BR

/Erik

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Based on my test, we can take the following steps to meet your requirement.

1. Enter a time table from 0:00:00-23:00:00.

2.Create the table using the formula as below.

DateTime = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2012, 1, 1 ), DATE ( 2020, 12, 31 ) ),
       'Time'  
    ),
    "DateTime", [Date] + [Time]
)

3. Then we can get the result as we need.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/8z0il9k8vy8l588/time.pbix?dl=0

Regards,

Frank

[donsvensen · Accepted solution]

Hi

You can create it like this in Power Query

let
    Source = List.Dates(#date(2018,3,25), 1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each {0..23}),
    #"Expanded {0}" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded {0}", "DateTime", each DateTime.From([Date])+#duration(0,[Custom],0,0))
in
    #"Added Custom1"

Modify the first step to create a longer period.

BR

/Erik