Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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. | Training | John Smith | 8/27/2023 | 0.02 | 35 | 1 |
ABC Co. | Operations | John Smith | 8/27/2023 | 7.07 | 35 | 2 |
ABC Co. | Technical | John Smith | 8/27/2023 | 2.7 | 35 | 3 |
XYZ Co. | Technical | John Smith | 8/28/2023 | 12.22 | 35 | 4 |
ABC Co. | Operations | John Smith | 8/29/2023 | 16.08 | 35 | 5 |
XYZ Co. | Training | John Smith | 8/30/2023 | 12.78 | 35 | 6 |
XYZ Co. | Technical | John Smith | 8/30/2023 | 3.77 | 35 | 7 |
ABC Co. | Operations | John Smith | 8/31/2023 | 10.43 | 35 | 8 |
XYZ Co. | Technical | John Smith | 9/1/2023 | 13.92 | 35 | 9 |
XYZ Co. | Operations | John Smith | 9/2/2023 | 13.6 | 35 | 10
|
Final data needs to be
CustomerDepartmentEmployeelocal_dateRegular HoursOvertime HoursWeek of YearIndex
ABC Co. | Training | John Smith | 8/27/2023 | 0.02 | 0 | 35 | 1 |
ABC Co. | Operations | John Smith | 8/27/2023 | 7.07 | 0 | 35 | 2 |
ABC Co. | Technical | John Smith | 8/27/2023 | 0.91 | 1.79 | 35 | 3 |
XYZ Co. | Technical | John Smith | 8/28/2023 | 8 | 4.22 | 35 | 4 |
ABC Co. | Operations | John Smith | 8/29/2023 | 8 | 8.08 | 35 | 5 |
XYZ Co. | Training | John Smith | 8/30/2023 | 8 | 4.78 | 35 | 6 |
XYZ Co. | Technical | John Smith | 8/30/2023 | 0 | 3.77 | 35 | 7 |
ABC Co. | Operations | John Smith | 8/31/2023 | 8 | 2.43 | 35 | 8 |
XYZ Co. | Technical | John Smith | 9/1/2023 | 0 | 13.92 | 35 | 9 |
XYZ Co. | Operations | John Smith | 9/2/2023 | 0 | 13.6 | 35 | 10 |
Solved! Go to 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
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
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
You are amazing!!!! Thank you so much.
I used your table
and ended up with a slightly different result than you were looking for.
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"
Proud to be a Super User! | |
Thank you so much, I will definately start working on it.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
16 | |
10 | |
9 | |
9 | |
8 |
User | Count |
---|---|
23 | |
16 | |
14 | |
12 | |
12 |