Forum Discussion
Merge a table based on half hour time
It's working how I intended it to work but feel free to round down instead of up or make whatever other adjustments you need for your particular use case. This is just a piece of the puzzle.
It will probably be useful to add a column to your second table that maps each time to a half-hour bucket. You can then do merge/join/group by operations using this column.
There's got to be a better way to discretize datetime but here's one possible method:
Table.AddColumn(previousStepName, "HalfHourBucket", each
#datetime(
Date.Year([#"Date/Time Effective"]),
Date.Month([#"Date/Time Effective"]),
Date.Day([#"Date/Time Effective"]),
Time.Hour([#"Date/Time Effective"]),
Number.RoundUp(Time.Minute([#"Date/Time Effective"])/30) * 30,
0
), type datetime
)
Thank you Alexis,
Your code gives the half hour rounded down i.e the end of the bucket rather than the start
HalfHourBucket
where the original column starts exactly on the hour or half hour its fine but where it starts after the half hour or hour it rounds to the next half hour.
| ||||||||
It's working how I intended it to work but feel free to round down instead of up or make whatever other adjustments you need for your particular use case. This is just a piece of the puzzle.