Group By Based on Sets of Dates / MIN Date

[Linnil]

Hi Everyone

I have timesheets and Employees who take Leave, and the Leave might stop and start in the same pay period.
In my example, the EMP has taken the Leave in 2 "blocks".

What I have		What I would like
EMP NO	Leave Type	Hours	Date		EMP NO	Leave Type	Hours	Date	Start Date	End Date
1055	86	8	15/01/2024		1055	86	31		15/01/2024	18/01/2024
1055	86	7	16/01/2024
1055	86	8	17/01/2024
1055	86	8	18/01/2024
1055			19/01/2024
1055			20/01/2024
1055	86	8	21/01/2024		1055	86	40		21/01/2024	25/01/2024
1055	86	8	22/01/2024
1055	86	8	23/01/2024
1055	86	8	24/01/2024
1055	86	8	25/01/2024
1055			26/01/2024

I am running a Group By Based on Emp and Leave Type and Min Date.
However my Min Date only gives one Min Dates being 15Jan24.
1) So if I can get 2 Min Dates that would make the Group By work better.

Because the Group By only finds one Min Date, I get 71 Hrs and it can count that there were 9 days (I just gave each date line value 1 and summed) BUT it adds 9 days to Min Date so I get Max Date as 23Jan24.

Hopefully you get the idea.
I can't remove dates as they are being used by other columns in the table.

I coud perhaps "short circuit" the block of dates with a dummy Leave Type Code, so if the code changes, the Min Date resets and looks for the "next" Leave Type code again.

I can get my PQ code to work perfectly if the dates are continuous, but of course it's never that easy ...

Any help appreciated - TIA

PS "What I would like" the empty rows are not necessary, only for explaining my problem

[dufoq3 · Accepted solution]

Hi @Linnil, try grouping with 4th argument GroupKind.Local.

Comment: I'm not sure if you want to group also by Leave Type or not.

Result

v1 GroupKind.Local

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNVXSUbIwAxFAbGiqb2Cob2RgZKIUq4MqbQ6SNsMpDdZtjl/aApu0AhQbWuKTNTLAa7SRIX5pI/zSxvilTfBLYw0zuMORgiwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"EMP NO" = _t, #"Leave Type" = _t, Hours = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Hours", Int64.Type}}),
    GroupedRowsLocal = Table.Group(ChangedType, {"EMP NO", "Leave Type"}, {{"Hours", each List.Sum([Hours]), type nullable number}, {"Start Date", each List.Min([Date]), type nullable date}, {"End Date", each List.Max([Date]), type nullable date}}, GroupKind.Local),
    FilteredRows = Table.SelectRows(GroupedRowsLocal, each ([Hours] <> null))
in
    FilteredRows

v2 GroupKind.Global (if you don't want to use v1 with GroupKind.Local for some reason, you can check this one with default grouping method)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwNVXSUbIwAxFAbGiqb2Cob2RgZKIUq4MqbQ6SNsMpDdZtjl/aApu0AhQbWuKTNTLAa7SRIX5pI/zSxvilTfBLYw0zuMORgiwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"EMP NO" = _t, #"Leave Type" = _t, Hours = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Hours", Int64.Type}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    Ad_GroupHelper = Table.AddColumn(AddedIndex, "GroupHelper", each if [Hours] = null then [Index] else null, Int64.Type),
    FilledUp = Table.FillUp(Ad_GroupHelper,{"GroupHelper"}),
    FilteredRows = Table.SelectRows(FilledUp, each ([Hours] <> null)),
    GroupedRows = Table.Group(FilteredRows, {"EMP NO", "GroupHelper"}, {{"Hours", each List.Sum([Hours]), type nullable number}, {"Start Date", each List.Min([Date]), type nullable date}, {"End Date", each List.Max([Date]), type nullable date}})
in
    GroupedRows

[Linnil]

Hi @dufoq3 - thanks for taking time to look at this.
v1 worked fine - I had actually got rid of the Null values for my hours earlier (though I showed it in my example) so I reinstated null hour values and it worked very well.

FYI Actually I find I have to group on 4 fields or more, so to save any pain, I just merge the fields I need to group by with a delimited, do my group by, and then split them by delimited when I have my grouping solutions. Makes group by a lot easier when there's just one field (and I'm always nervous grouping by more than one field, that's just me!)

Thanks again and best wishes 🙂

[wdx223_Daniel]

NewStep=Table.Group(WhatYouHave,{"EMP NO","Leave Type"},{{"Hours",each List.Sum([Hours])},{"Start Date",each List.Min(List.RemoveNulls([Date]))},{"End Date",each List.Max(List.RemoveNulls([Date]))}},0,(x,y)=>Byte.From(x[EMP NO]<>y[EMP NO] or y[Leave Type]=null))

[Linnil]

Hi @wdx223_Daniel  Thanks for your input. I tried that solution - it gave me a total of 71 hours and Start Date 15Jan24 End Date 26Jan24. That was the point I got stuck on myself when I was coding.
The solutions above captured the break in the days / 2 lots of sub totals.
Thanks again