Day repeated 24 times for a year

Power BI
1

Hello guys,

I am trying to set up an excel \ power query or power bi document with an hourly breakdown for an entire year. However, I cannot figure out how to do this without having to hand enter the date/time, due to the day needing to be repeated 24 times before moving onto the next day and therefore does not follow a pattern that column from example will recognize and autopopulate.

I.e. I nee something that looks like this:

08/01/2013 00:00

08/01/2013 01:00

08/01/2013 02:00

please help. thanks.

2

Hello Nuhea,

Will this work for you?

‘Enter Data’ to make your own table for 24 hours in a day. Name Table = ‘Hours’ and Column = ‘Hours’ then type row values of 00,01,etc to 24. Convert Column type to Time.

Then do the following formula to Cross Join and Add your Dates to the Times:

Date/Time =
SELECTCOLUMNS(
ADDCOLUMNS(
CROSSJOIN( Dates, Hours ),
“DateTime”, Dates[Date]+Hours[Hours]
),
“Date/Time”,[DateTime]
)

Hopefully this is what you meant by your request.

Andrew