How to generate date and time interval list using Power Query

One thing I love about Microsft Power Query is the ability to make lists. It means that you can generate data without some additional source. I will show you how to make list with the last seven days and 15 minute time intervals. It is useful when you combine it with data, where several periods are missing or not represented. Full M code is at the end of the post.

Let’s start with the last 7 days. Open Blank Query and in the formula bar write List.Dates function.

= List.Dates(DateTime.Date(DateTime.LocalNow()), 7, #duration(-1, 0, 0, 0))

DateTime.Date(DateTime.LocalNow()) gets todays date in Power Query,
7 represets number un days and #duration(-1, 0, 0, 0) decrementing by one day.
The result looks like this.

Transform it to a table without changing any additional settings.

After that, I renamed my column to Date.
Let’s continue with 15 minute time intervals. Go to Add Column -> Add Custom Column, give it a name Time and in the custom column formula write List.Times function.

= List.Times(#time(0, 0, 0), 96, #duration(0, 0, 15, 0))

96 represents a count of 15-minute periods in 24 hours and #duration incrementing from 00:00:00 with 15-minute steps.

Expand Time column to new rows, change both columns data type and result look like this.

If you want a date in time in one column, then add a new column and combine date and time with & symbol.

The result looks like this.

Take this M code from all this process and use it in Blank Query.

let
    Source = List.Dates(DateTime.Date(DateTime.LocalNow()), 7, #duration(-1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Time", each List.Times(#time(0, 0, 0), 96, #duration(0, 0, 15, 0))),
    #"Expanded Time" = Table.ExpandListColumn(#"Added Custom", "Time"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Time",{{"Time", type time}, {"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "DateTime", each [Date]&[Time]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"DateTime", type datetime}})
in
    #"Changed Type1"