Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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 🙂
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))
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