I have a time table as depicted in the attachment below.
I can't merge with a certain column because the time of 12:00:00 AM doesn't have a match with my table.
There are 86,400 seconds in a given 24 hour period. I have recorded 0..86399 seconds because if I place 0..86400, it will error in the calculations. This is because second number zero = 86400.
Problem: I am not sure why, but it starts with 12:00:01 AM and not 12:00:00, probably because of the previously stated logic.
Please help me figure out a way to code 12:00:00 AM into the M Code. Thanks!
Post Script:
Here is my code:
let
Source = {0..86399},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Promoted Headers" = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"0", "Seconds Key"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Seconds Key", Int64.Type}}),
#"Inserted Division" = Table.AddColumn(#"Changed Type", "Division", each [Seconds Key] / 86400, type number),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Division",{{"Division", type time}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Division", "Seconds"}}),
#"Inserted Integer-Division" = Table.AddColumn(#"Renamed Columns1", "Integer-Division", each Number.IntegerDivide([Seconds Key], 300), Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Integer-Division", "5-Minute Grouping", each [#"Integer-Division"]*300/86400),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"5-Minute Grouping", type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Integer-Division"}),
#"Inserted Integer-Division1" = Table.AddColumn(#"Removed Columns", "Integer-Division", each Number.IntegerDivide([Seconds Key], 900), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Inserted Integer-Division1", "15-Minute Grouping", each [#"Integer-Division"]*900/86400),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"15-Minute Grouping", type time}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type3",{"Integer-Division"}),
#"Inserted Integer-Division2" = Table.AddColumn(#"Removed Columns1", "Integer-Division", each Number.IntegerDivide([Seconds Key], 1800), Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Inserted Integer-Division2", "30-Minute Grouping", each [#"Integer-Division"]*1800/86400),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom2",{{"30-Minute Grouping", type time}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type4",{"Integer-Division"}),
#"Inserted Integer-Division3" = Table.AddColumn(#"Removed Columns2", "Integer-Division", each Number.IntegerDivide([Seconds Key], 3600), Int64.Type),
#"Added Custom3" = Table.AddColumn(#"Inserted Integer-Division3", "Hourly Grouping", each [#"Integer-Division"]),
#"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Hourly Grouping"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns3", "Hourly Grouping", each [#"Integer-Division"]*3600/86400),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Custom4",{{"Hourly Grouping", type time}}),
#"Removed Columns4" = Table.RemoveColumns(#"Changed Type5",{"Integer-Division"}),
#"Inserted Integer-Division4" = Table.AddColumn(#"Removed Columns4", "Integer-Division", each Number.IntegerDivide([Seconds Key], 14400), Int64.Type),
#"Added Custom5" = Table.AddColumn(#"Inserted Integer-Division4", "AM/PM", each [#"Integer-Division"]*14400/86400),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Custom5",{{"AM/PM", type time}}),
#"Removed Columns5" = Table.RemoveColumns(#"Changed Type6",{"Integer-Division"}),
#"Inserted Integer-Division5" = Table.AddColumn(#"Removed Columns5", "Integer-Division", each Number.IntegerDivide([Seconds Key], 28800), Int64.Type),
#"Added Custom6" = Table.AddColumn(#"Inserted Integer-Division5", "8-Hour Grouping", each [#"Integer-Division"]*28800/86400),
#"Removed Columns6" = Table.RemoveColumns(#"Added Custom6",{"Integer-Division"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Removed Columns6",{{"8-Hour Grouping", type time}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type7",{"Seconds", "5-Minute Grouping", "15-Minute Grouping", "30-Minute Grouping", "Hourly Grouping", "AM/PM", "8-Hour Grouping", "Seconds Key"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"AM/PM", "4-Hour Grouping"}, {"Hourly Grouping", "1-Hour Grouping"}})
in
#"Renamed Columns2"
Helpful resources
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
