Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
LydaMosquera
New Member

Need to add worked hours on various rows without grouping. I am using Power Query.

Criteria - any daily hours over 8 is considered overtime hours and any hours over 40 in a week is also overtime hours.  I cannot group since I need keep original data on columns and rows intact for later grouping by customer.  

 

Original data - 

CustomerDepartmentEmployeelocal_dateHoursWeek of YearIndex

ABC Co. TrainingJohn Smith8/27/20230.02351
ABC Co. OperationsJohn Smith8/27/20237.07352
ABC Co. TechnicalJohn Smith8/27/20232.7353
XYZ Co. TechnicalJohn Smith8/28/202312.22354
ABC Co. OperationsJohn Smith8/29/202316.08355
XYZ Co. TrainingJohn Smith8/30/202312.78356
XYZ Co. TechnicalJohn Smith8/30/20233.77357
ABC Co. OperationsJohn Smith8/31/202310.43358
XYZ Co. TechnicalJohn Smith9/1/202313.92359
XYZ Co. OperationsJohn Smith9/2/202313.635

10

 

 

 

Final data needs to be 

CustomerDepartmentEmployeelocal_dateRegular HoursOvertime HoursWeek of YearIndex

ABC Co. TrainingJohn Smith8/27/20230.020351
ABC Co. OperationsJohn Smith8/27/20237.070352
ABC Co. TechnicalJohn Smith8/27/20230.911.79353
XYZ Co. TechnicalJohn Smith8/28/202384.22354
ABC Co. OperationsJohn Smith8/29/202388.08355
XYZ Co. TrainingJohn Smith8/30/202384.78356
XYZ Co. TechnicalJohn Smith8/30/202303.77357
ABC Co. OperationsJohn Smith8/31/202382.43358
XYZ Co. TechnicalJohn Smith9/1/2023013.92359
XYZ Co. OperationsJohn Smith9/2/2023013.63510

 

1 ACCEPTED SOLUTION

@LydaMosquera try this

let
    Source = your_table,
    fx_calc = (tbl as table) =>
        [a = List.Buffer(Table.ToRecords(tbl)),
        maxx = List.Count(a),
        b = 
            List.Generate(
                () => 
                    [i = 0,
                    new_day = false,
                    hours = a{0}[Hours],
                    lim_day_start = 8,
                    Regular = List.Min({hours, 8}),
                    Overtime = hours - Regular,
                    lim_day = List.Max({0, 8 - hours}),
                    lim_week = List.Max({0, 40 - Regular})],
                (x) => x[i] < maxx,
                (x) => 
                    [i = x[i] + 1,
                    new_day = a{i-1}[local_date] <> a{i}[local_date],
                    hours = a{i}[Hours],
                    lim_day_start = if new_day then 8 else x[lim_day],
                    Regular = List.Min({hours, lim_day_start, x[lim_week]}),
                    Overtime = hours - Regular,
                    lim_day = List.Max({0, lim_day_start - hours}),
                    lim_week = List.Max({0, x[lim_week] - Regular})],
                (x) => x[[Regular], [Overtime]]
            ),
            z = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) & {"hrs"})][z],
    group = Table.Group(Source, {"Employee", "Week of Year"}, {{"ee_week", each fx_calc(Table.Sort(_, "Index"))}}),
    expand_ee = Table.ExpandTableColumn(group, "ee_week", {"Customer", "Department", "local_date", "Hours", "Index", "hrs"}),
    expand_hrs = Table.ExpandRecordColumn(expand_ee, "hrs", {"Regular", "Overtime"})
in
    expand_hrs

OT2.jpg

View solution in original post

6 REPLIES 6
AlienSx
Super User
Super User

Hi, @LydaMosquera can't figure out why Table.Group is prohibited if you can Table.ExpandTableColumn in the end. Original data are intact. Your calculations of regular and overtime are wrong - I agree with @jgeddes 

let
    Source = your_table,
    fx_calc = (tbl as table) =>
        [a = List.Buffer(Table.ToRecords(tbl)),
        maxx = List.Count(a),
        b = 
            List.Generate(
                () => 
                    [i = 0,
                    new_day = false,
                    hours = a{0}[Hours],
                    lim_day_start = 8,
                    Regular = List.Min({hours, 8}),
                    Overtime = hours - Regular,
                    lim_day = List.Max({0, 8 - hours}),
                    lim_week = List.Max({0, 40 - hours})],
                (x) => x[i] < maxx,
                (x) => 
                    [i = x[i] + 1,
                    new_day = a{i-1}[local_date] <> a{i}[local_date],
                    hours = a{i}[Hours],
                    lim_day_start = if new_day then 8 else x[lim_day],
                    Regular = List.Min({hours, lim_day_start, x[lim_week]}),
                    Overtime = hours - Regular,
                    lim_day = List.Max({0, lim_day_start - hours}),
                    lim_week = List.Max({0, x[lim_week] - hours})],
                (x) => x[[Regular], [Overtime]]
            ),
            z = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) & {"hrs"})][z],
    group = Table.Group(Source, {"Employee", "Week of Year"}, {{"ee_week", each fx_calc(Table.Sort(_, "Index"))}}),
    expand_ee = Table.ExpandTableColumn(group, "ee_week", {"Customer", "Department", "local_date", "Hours", "Index", "hrs"}),
    expand_hrs = Table.ExpandRecordColumn(expand_ee, "hrs", {"Regular", "Overtime"})
in
    expand_hrs

OT.jpg

 

Thank you so much for helping me.  Maybe I did not explain the OT criteria well.  Overtime is based daily first, any hour over 8 per each day is OT.   Once Regular time reaches 40 in a week, then all hours after are OT as well.  I am trying to change the formula to meet above criteria but am having trouble doing so.  I am a rookie in power query.  Again, I truly appreciate your help.  

@LydaMosquera try this

let
    Source = your_table,
    fx_calc = (tbl as table) =>
        [a = List.Buffer(Table.ToRecords(tbl)),
        maxx = List.Count(a),
        b = 
            List.Generate(
                () => 
                    [i = 0,
                    new_day = false,
                    hours = a{0}[Hours],
                    lim_day_start = 8,
                    Regular = List.Min({hours, 8}),
                    Overtime = hours - Regular,
                    lim_day = List.Max({0, 8 - hours}),
                    lim_week = List.Max({0, 40 - Regular})],
                (x) => x[i] < maxx,
                (x) => 
                    [i = x[i] + 1,
                    new_day = a{i-1}[local_date] <> a{i}[local_date],
                    hours = a{i}[Hours],
                    lim_day_start = if new_day then 8 else x[lim_day],
                    Regular = List.Min({hours, lim_day_start, x[lim_week]}),
                    Overtime = hours - Regular,
                    lim_day = List.Max({0, lim_day_start - hours}),
                    lim_week = List.Max({0, x[lim_week] - Regular})],
                (x) => x[[Regular], [Overtime]]
            ),
            z = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) & {"hrs"})][z],
    group = Table.Group(Source, {"Employee", "Week of Year"}, {{"ee_week", each fx_calc(Table.Sort(_, "Index"))}}),
    expand_ee = Table.ExpandTableColumn(group, "ee_week", {"Customer", "Department", "local_date", "Hours", "Index", "hrs"}),
    expand_hrs = Table.ExpandRecordColumn(expand_ee, "hrs", {"Regular", "Overtime"})
in
    expand_hrs

OT2.jpg

You are amazing!!!!  Thank you so much.  

jgeddes
Super User
Super User

I used your table

jgeddes_0-1697138541505.png

and ended up with a slightly different result than you were looking for.

jgeddes_1-1697138590240.png

My numbers are different on 8/30/2023 as there was 1.91 hours left in the 40 hour limit before OT kicked in.
I also was not sure why there was 8 hours of Regular Time on 8/31/2023 when the 40 threshold had been hit.

Anyways, you can play with the following code to get the results you are looking for.

let
Source =
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldKxDoIwEAbgVyGdzXH0gJZR2VwcZFAJAyFEmmgxyPvHxqQFMRBY2uvy5b8/zXO2P6Re2oLHdizrSqWVvpvx2DbaOz9V35iH9LnwOXIyMwJyc1FkjoAVux/g9Kq7sletfi8RAlBYgk+JrK4ararysSRwcAB9gcv1tgaQFgg4cLdGuGmNxBkxoLRG9BdjtkvCUQrhhHjDIgNBIFwVYsseFLgUCCFZQ65OkfiDQJC4NpOpMJ/CVDkiYvetkBXFBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Department = _t, Employee = _t, local_date = _t, Hours = _t, #"Week of Year" = _t, Index = _t]),
#"Changed Type" =
Table.TransformColumnTypes(
Source,
{{"Customer", type text}, {"Department", type text}, {"Employee", type text}, {"local_date", type date}, {"Hours", type number}, {"Week of Year", Int64.Type}, {"Index", Int64.Type}}
),
Custom1 =
Table.AddColumn(
#"Changed Type",
"_hoursWeekToDate",
each
List.Sum(
Table.SelectRows(
#"Changed Type",
//this selects all rows where the 'employee' and 'week of year' match the current row and where the 'local_date'
//is less than or equal to the 'local_date' of the current row and the 'index' is less then or equal to
//the current row index
(x)=> x[Employee] = [Employee] and x[Week of Year] = [Week of Year] and x[local_date] <= [local_date] and x[Index] <= [Index]
)[Hours]
),
type number
),
#"Added Custom" =
Table.AddColumn(
Custom1,
"Regular Hours",
each
if [_hoursWeekToDate] <= 40 and [Hours] < 8
then [Hours]
else if [_hoursWeekToDate] <= 40 and [Hours] >= 8
then 8
else if [_hoursWeekToDate] > 40
then if 40 - ([_hoursWeekToDate] - [Hours]) > 0
then 40 - ([_hoursWeekToDate] - [Hours])
else 0
else null,
type number
),
#"Added Custom1" =
Table.AddColumn(
#"Added Custom",
"Overtime Hours",
each
[Hours] - [Regular Hours],
type number
)
in
#"Added Custom1" 

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thank you so much, I will definately start working on it. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors