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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors