Power Query Grouping challenge

[Mahmed1]

Hi All

i have a massive data set where i have 

Date

Area

Code

start time 09:15

end time 09:45

length (diff between end time - start time) 30mins

what i need to do is look at the length so 30 mins in this example and group by half hour intervals

so based on the above example 15 mins of the length was between 09:00 to 09:30 and other 15 mins was between 09:30 and 10:00

i need to do that for each row and group into half hour intervals for that date, area and code

i hope im making sense but this is a massive challenge for me

essentially i need a revised view that splits the length by 30 mins intervals from 00:00-00:30,01:00....23:30

[BA_Pete · Accepted solution]

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

[Mahmed1]

Hi @BA_Pete 

thanl you

• i still get an issue at some stage with same error even if i take grouping out increment argument out if range error

[BA_Pete]

It sounds like the code has got corrupted somewhere.

If you start from the Source step and click through each step one by one, this should identify exactly which step is causing the error. If it's one of my steps, then you'll need to copy the code out of Advanced Editor for the whole query and paste it into a code window ( </> button ) here. Make sure to anonymise any connection strings etc.

Pete

[ttttttt]

Hi @BA_Pete  - for some reason it wouldnt let me sign or respond from my account so had to recreate a username an register to post from a different device

This is the full code but im still getting that same error

let
    Source = Access.Database(File.Contents("S:\CR\Secure\P&S Resource Planning\Hub\Databases\Planning Pack Database\Results_Shrinkage.accdb"), [CreateNavigationProperties=true]),
    _tbl_Rolling_WFM_agentResults_PP = Source{[Schema="",Item="tbl_Rolling_WFM_agentResults_PP"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(_tbl_Rolling_WFM_agentResults_PP,{{"Date_", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"TZ", "custID", "acdID", "logonID", "ssn", "modify"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"start", type time}, {"stop", type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [muID] = 2005),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNDays([Date_], 3)),
  
    addHalfHourBins =
        Table.AddColumn(
            #"Filtered Rows1",
            "halfHourBins",
            each let
                startHalfHour = Time.From(Number.RoundDown(48 * Number.From([start]) / 1 ) / 48),
                endHalfHour = Time.From(Number.RoundUp(48 * Number.From([stop]) / 1 ) / 48)
            in
            List.Times(
                startHalfHour,
                Duration.TotalMinutes(endHalfHour - startHalfHour) / 30,
                #duration(0,0,30,0)
            )
        ),
    expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
    addHalfHourBinDuration =
        Table.AddColumn(
            expandHalfHourBins,
            "halfHourBinDuration",
            each Duration.TotalMinutes(
                List.Min({[stop], [halfHourBins] + #duration(0,0,30,0)})
                - List.Max({[start], [halfHourBins]})
            )
        )
in
    addHalfHourBinDuration

[BA_Pete]

No problem.

My initial guess is that you have some start/stop times that go over midnight.

Are you able to share an example of your actual data as at your #"Filtered Rows1" step please?

you can select this query step, copy the whole table (if below 3,000 cells total), paste into 'Enter Data' then copy the M code form that query and paste here.

Pete

[ttttttt]

@BA_Pete - hi yes ive seen a few agents where he stop time over laps into the next day

so if an agents start was 23:00 and end 00:05 it causes an issue

Im trying to copy limited data but having issues

[BA_Pete · Accepted solution]

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

[ttttttt]

@BA_Pete 

Thank you would this still mean that anytime overlapped will still go in previous days bucket or the 18th?

eg

17/05/2023  22:50 00:05 (so the 5 mins even though overlapped) would count towards 18/05/2023 @ 00:00 or 17/05/2023 @ 00:00 (im assuming the 17th)

[BA_Pete]

If you're changing [halfHourBins] to Time type then grouping on [Date_] and [halfHourBins], then it will essentially get summed up within whatever date [Date_] is.

Alternatively, you could leave [halfHourBins] as DateTime type and just group on [halfHourBins] instead, as this now contains the exact/correct date as well as the half hour slot.

Pete

[ttttttt]

thank you so much - would be awesome if you can break down how the core bit works just so i understand the logic

[BA_Pete]

Crikey, you don't ask for much, do you! 😂

Which bit(s) in particular are you not sure about?

Pete

[Mahmed1]

Thank you haha

just the main core bits how the functions are working and why divide by 1/48 etc

any commentary on main core bits

i understand it briefly but i get you are generating times then lopping over it?

[BA_Pete · Accepted solution]

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

[Mahmed1]

Thank you

youve been so helpful 

[BA_Pete]

Hi @Mahmed1 ,

Try this example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrC0MjRV0gHRJqZKsTrRSoYGVoYWQBFDQysTI4iImZWBAUjEDK7G0srYCChiZGhlaq4UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startTime = _t, endTime = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"startTime", type time}, {"endTime", type time}}),
    
// Relevant steps from here ----->
    addHalfHourBins =
        Table.AddColumn(
            chgTypes,
            "halfHourBins",
            each let startHalfHour = Time.From(Number.RoundDown(48 * Number.From([startTime]) / 1 ) / 48) in
            List.Times(
                startHalfHour,
                Duration.TotalMinutes(Time.EndOfHour([endTime]) - startHalfHour) / 30,
                #duration(0,0,30,0)
            )
        ),
    expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
    addHalfHourBinDuration =
        Table.AddColumn(
            expandHalfHourBins,
            "halfHourBinDuration",
            each Duration.TotalMinutes(
                List.Min({[endTime], [halfHourBins] + #duration(0,0,30,0)})
                - List.Max({[startTime], [halfHourBins]})
            )
        )
in
    addHalfHourBinDuration

For this output:

Pete

[Mahmed1]

@BA_Pete 

Hi thank you

It nearly works but ive come across an issue

for eg - in this example - For finance 08:00 - 09:00 - i should get 30 mins from 08:00 to 08:30 and 30 mins from 08:30 - 09:00. I get that however i am getting a minus figure at 09:30

OutPut

DateAreaActivitystartTimeendTimeLengthhalfHourBinshalfHourBinDuration

18/05/2023	Finance	Open	08:00:00	09:00:00	60	08:00:00	30
18/05/2023	Finance	Open	08:00:00	09:00:00	60	08:30:00	30
18/05/2023	Finance	Open	08:00:00	09:00:00	60	09:00:00	0
18/05/2023	Finance	Open	08:00:00	09:00:00	60	09:30:00	-30
18/05/2023	IT	Open	09:00:00	09:45:00	45	09:00:00	30
18/05/2023	IT	Open	09:00:00	09:45:00	45	09:30:00	15

Input - DateAreaActivitystartTimeendTimeLength

18/05/2023	Finance	Open	08:00	09:00	60
18/05/2023	IT	Open	09:00	09:45	45

[Mahmed1]

Thank you - you are amazing 

ive not tried this yet but cant wait to give this a go

If i had additional columns

Date

Area

ActivityType

how can i group so it buckets it like you did it but for the date, area and activity?

[Mahmed1]

@BA_Pete 

I think the error or minus figure occurs when the bins doesnt go into next 30 mins

eg if start was 09:15 to 09:29

i get

09:00 = 14

09:30 = -1

If i do 08:45 to 09:00

i get 

08:30 = 15

09:00 = 0

09:30 = -30

So needs a slight tweak somewhere 🙂

Again thank you so so much

[BA_Pete]

Well spotted, sorry about that.

Try this version that fixes the 'same half hour' issue:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcuxDcAwCETRXagpgBjHsApi/zUMdpTqpMcnAsicFbB3KCQGMDmvEmYfcmU6Ucv8G/NHSoRd3yP1f5pasSura/wumRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startTime = _t, endTime = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"startTime", type time}, {"endTime", type time}}),
    
    addHalfHourBins =
        Table.AddColumn(
            chgTypes,
            "halfHourBins",
            each let
                startHalfHour = Time.From(Number.RoundDown(48 * Number.From([startTime]) / 1 ) / 48),
                endHalfHour = Time.From(Number.RoundUp(48 * Number.From([endTime]) / 1 ) / 48)
            in
            List.Times(
                startHalfHour,
                Duration.TotalMinutes(endHalfHour - startHalfHour) / 30,
                #duration(0,0,30,0)
            )
        ),
    expandHalfHourBins = Table.ExpandListColumn(addHalfHourBins, "halfHourBins"),
    addHalfHourBinDuration =
        Table.AddColumn(
            expandHalfHourBins,
            "halfHourBinDuration",
            each Duration.TotalMinutes(
                List.Min({[endTime], [halfHourBins] + #duration(0,0,30,0)})
                - List.Max({[startTime], [halfHourBins]})
            )
        )
in
    addHalfHourBinDuration

Pete

[Mahmed1]

Thank you - seems to be ok but having issues with grouping 

=Table.Group(addHalfHourBinDuration, {"Date_","muID","exception","halfHourBins"},{{"Total",each List.Sum([halfHourBinDuration}), type number}})

Getting this error - Expression.error - The increment argument is out of range 0.00:30:00

[BA_Pete]

The code looks fine, apart from this bit:

This curly bracket '}' should be square bracket ']'

Pete