Display records by the hour

Hi Anonymous ,

Paste this into a new blank query in Power Query. It's a time dimension table:

let
  hoursTable = Table.FromList({0..23}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  renHours = Table.RenameColumns(hoursTable, {{"Column1", "hours"}}),
  minutesTable = Table.FromList({0..59}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  secondsTable = Table.FromList({0..59}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  addCJminutes = Table.AddColumn(renHours, "minutes", each minutesTable),
  expandCJminutes = Table.ExpandTableColumn(addCJminutes, "minutes", {"Column1"}, {"minutes"}),
  addCJseconds = Table.AddColumn(expandCJminutes, "seconds", each secondsTable),
  expandCJseconds = Table.ExpandTableColumn(addCJseconds, "seconds", {"Column1"}, {"seconds"}),
  addTimeHH = Table.AddColumn(expandCJseconds, "timeHH", each Text.Combine({Text.From([hours], "en-GB"), "00"}, ":")),
  addTimeHHMM = Table.AddColumn(addTimeHH, "timeHHMM", each Text.Combine({Text.From([hours], "en-GB"), Text.From([minutes], "en-GB")}, ":"), type text),
  addTimeHHMMSS = Table.AddColumn(addTimeHHMM, "timeHHMMSS", each Text.Combine({Text.From([hours], "en-GB"), Text.From([minutes], "en-GB"), Text.From([seconds], "en-GB")}, ":"), type text),
  chgAllTypes = Table.TransformColumnTypes(addTimeHHMMSS, {{"timeHH", type time}, {"hours", Int64.Type}, {"minutes", Int64.Type}, {"seconds", Int64.Type}, {"timeHHMM", type time}, {"timeHHMMSS", type time}})
in
  chgAllTypes

Next, apply this new table to your data model, and relate time[timeHHMMSS] to yourTable[Timestamp].

Create measures for each of your values that you want to display using your required aggregation e.g.:

_shipTotal = SUM(yourTable[Ship Total Fuel Cons])

Use the time[hours] column and your new measures in your visuals to group measure totals by hours.

Pete