Time Table (Hour and Minute Buckets) in Power Query M

Generates a standalone table of every minute in a day, useful for time-of-day analysis and slicers.

Date & TimeIntermediatetime tablehourminutecalendar

Power Query M code

let
    Times = List.Times(#time(0, 0, 0), 1440, #duration(0, 0, 1, 0)),
    ToTable = Table.FromList(Times, Splitter.SplitByNothing(), {"Time"}),
    ChangedType = Table.TransformColumnTypes(ToTable, {{"Time", type time}}),
    AddedHour = Table.AddColumn(ChangedType, "Hour", each Time.Hour([Time]), Int64.Type),
    AddedMinute = Table.AddColumn(AddedHour, "Minute", each Time.Minute([Time]), Int64.Type),
    AddedHourBucket = Table.AddColumn(AddedMinute, "HourBucket", each Text.PadStart(Text.From([Hour]), 2, "0") & ":00", type text)
in
    AddedHourBucket

Generate a date table in Power Query M

Notes

This produces one row per minute (1,440 rows); group HourBucket for hourly analysis.

Requirements

None - this is a standalone query.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Time Table (Hour and Minute Buckets).