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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors