Merge a table based on half hour time

[th036]

Hi All,

I am new to Power Query dispite. I have created a table breaking a year down to half hourly intervals as follows:

01/04/2021 00:00:00

01/04/2021 00:30:00

01/04/2021 01:00:00

01/04/2021 01:30:00

etc for the whole year

I then have a second table in Excel as follows:

Name	ID	Date/Time Effective	Status	Rate
Machine A	78	01/03/2021 23:07	Defective	25
Machine B	43	01/01/2021 00:00	Operational	75
Machine B	43	02/04/2021 15:26	Defective	75
Machine B	43	03/04/2021 17:02	Operational	75
Machine A	78	01/05/2021 08:00	Operational	25
Machine A	78	05/05/2021 17:08	Defective	25
Machine A	78	05/05/2021 19:21	Operational	25
Machine C	87	10/12/2020 01:30	Defective	15

what i want is to is for each Machine to have its own year table, then assign the status to each half hour as appropriate 

so Machine A output would be as follows:

Date/Time	Name	Status	Rate
01/04/2021 00:00	Machine A	Defective	25
01/04/2021 00:30
01/05/2021 08:00	Machine A	Operational	25
01/05/2021 08:30
05/05/2021 17:00	Machine A	Defective	25
05/05/2021 17:30
05/05/2021 18:00
05/05/2021 18:30
05/05/2021 19:00	Machine A	Operational	25

so the 01/04/00 00:00 half hour is populated as defective because that was the status going into the date range, it then changed on 01/05/21 08:00 to operational etc. I would then fill down and could run sums off the table.

I have a large number of status changes and could do in VBA with loops and arrays etc but i am trying to do in power query which i have only just become aware of.

Any help or guidance would be much appreciated. 

[AlexisOlson]

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
)

[th036]

Thank you Alexis,

Your code gives the half hour rounded down i.e the end of the bucket rather than the start

HalfHourBucket 01/03/2021 23:30:00 01/01/2021 00:00:00 02/04/2021 15:30:00 03/04/2021 17:30:00 01/05/2021 08:00:00 05/05/2021 17:30:00 05/05/2021 19:30:00 10/12/2020 01:30:00 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.

[AlexisOlson · Accepted solution]

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.

[artemus]

A for loop in power query looks like:

(year as number) as list => List.Generate(() => #date(year, 1, 1), each _ < #date(year + 1, 0, 0), each _ + #duration(0, 0, 30, 0)))

For here you can convert to a table if needed.