Power Query Grouping challenge

 

Try this version that fixes overnight issue. I've had to create your buckets as DateTime values to account for the date change overnight, but you can easily change this back to Time type afterwards if you prefer:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfg2KRxilVUa91+jWisxIbmPx8HzEMs1BgylRNxcbGU26mkxNgu71JniGcEaXJx55dHOwc0VIyFud7Adv7/2SLTgElzn0b22+d4M/dvsvH6rXIKDrUwWR6XeXw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date_ = _t, start = _t, stop = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"start", type time}, {"stop", type time}, {"Date_", type date}}),
    
    addHalfHourBins =
        Table.AddColumn(
            chgTypes,
            "halfHourBins",
            each let
                startDateTime = DateTime.From([Date_] & [start]),
                stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop]),
                startHalfHour = DateTime.From(Number.RoundDown(48 * Number.From(startDateTime) / 1 ) / 48),
                stopHalfHour = DateTime.From(Number.RoundUp(48 * Number.From(stopDateTime) / 1 ) / 48)
            in
            List.DateTimes(
                startHalfHour,
                Duration.TotalMinutes(stopHalfHour - startHalfHour) / 30,
                #duration(0,0,30,0)
            )
        ),
    expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
    addHalfHourBinDuration =
        Table.AddColumn(
            expandHalfHourBins,
            "halfHourBinDuration",
            each let
                startDateTime = DateTime.From([Date_] & [start]),
                stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop])
            in
            Duration.TotalMinutes(
                List.Min({stopDateTime, [halfHourBins] + #duration(0,0,30,0)})
                - List.Max({startDateTime, [halfHourBins]})
            )
        )
in
    addHalfHourBinDuration

 

Pete

 

Ok, so here's the basics:

addHalfHourBins =

//'let..in' lets us declare variables for later use

startDateTime = DateTime.From([Date_] & [start]) //This creates datetime values from date and time (so we can handle date changes).

stopDateTime = if [stop] < [start] then DateTime.From(Date.AddDays([Date_], 1) & [stop]) else DateTime.From([Date_] & [stop]) //This does the same but adds a day to datetime if stop time is before start time.

startHalfHour = DateTime.From(Number.RoundDown(48 * Number.From(startDateTime) / 1 ) / 48) //This rounds down the datetime value to the nearest half hour to provide the first value in our list of half-hours. It essentially converts the time to a decimal value, rounds down to the nearest half hour (this is the 48 bit - there's 48 half-hours in a day), then converts back to datetime.

stopHalfHour = DateTime.From(Number.RoundUp(48 * Number.From(stopDateTime) / 1 ) / 48) //Same as above, but rounds up so we know where the list needs to end.

List.DateTimes( //This creates a list of datetimes
    startHalfHour, //List starting value.
    Duration.TotalMinutes(stopHalfHour - startHalfHour) / 30, //How many list items to generate.
    #duration(0,0,30,0) //What time increments to use for each list item.
)

 

Hopefully the rest is fairly self-explanatory, but let me know if there'sanything else you're struggling with.

 

Also, feel free to give a thumbs-up on any posts that have helped you - that helps me keep doing my thang 🙂

 

Pete