Hi everyone, I want to make operations through Power Query. I am a rookie but I almost succeeded reaching my goal here but now I am stuck.
I have 5 columns which gives for each technician, type of hours spent (work, holidays, ...), the number of hours spent, the date when it was spent and the week number related. Here are more details about my current data, so we have the 1st column with names (named "Technician name"), the 2nd (named "Pay Code") giving the type of hours, meaning :
My need : compare "Holiday" hours (same as "Holiday - Shift 2" or "Holiday - Shift 3") with "Holiday Worked" hours (same as "Holiday Worked -Shift2" or "Holiday Worked -Shift3").
In the 2 examples for which I'll give more details, the workday of the technician is 10 hours (because for others it can be 8 hours).
In orange we have the case where "Holiday" in inferior to "Holiday Worked", meaning that the technician worked 11 hours during a public holiday where he should be at rest. In this case, I simply want to delete the row related to "Holiday".
SO if "Holiday" hours (or “Holiday - Shift 2” or “Holiday - Shift 3”) inferior to "Holiday Worked" hours (or "Holiday Worked -Shift2" or "Holiday Worked -Shift3") for the same day and same technician, then delete the entire row corresponding to "Holiday" for the same day and same technician.
In blue we have the case where "Holiday" is superior to "Holiday Worked", meaning that the technician worked 7.75 hours during a public holiday of 10 hours, in which 2.25 hours of rest hours left (10-7.75 = 2.25). In this case I need 2 things please, 1st calculate the difference "Holiday" minus "Holiday Worked". The result (2.25 here) will replace the value of "Holiday" (here replacing 10 by 2.25) to say that this day, the technician had only 2.25 hours of paid rest instead of 10 hours. And 2nd delete the row corresponding to "Holiday Worked" because we don't need it anymore as the worked time was absorbed.
Result:
I hope I was clear enough and don't hesitate to ask if more precisions are needed !
Here are the files if needed : https://file.io/PghHXLxKhuf6
Thanks !
I have 5 columns which gives for each technician, type of hours spent (work, holidays, ...), the number of hours spent, the date when it was spent and the week number related. Here are more details about my current data, so we have the 1st column with names (named "Technician name"), the 2nd (named "Pay Code") giving the type of hours, meaning :
- Worked hours (“Regular”, “Regular - Shift 2, “Regular - Shift 3”),
- Overtime (“Overtime”, “Overtime - Shift 2”, “Overtime - Shift 3”, “Overtime - Weekend", “Overtime - Weekend -Shift2”, “Overtime - Weekend -Shift3”, “Holiday Worked”, “Holiday Worked - Shift 2”, “Holiday Worked - Shift 3”),
- Days/hours off (“PTO”, “PTO - Shift 2”, “PTO -Shift 3”),
- Days/hours which are public holidays (“Holiday”, “Holiday - Shift 2”, “Holiday - Shift 3”).
My need : compare "Holiday" hours (same as "Holiday - Shift 2" or "Holiday - Shift 3") with "Holiday Worked" hours (same as "Holiday Worked -Shift2" or "Holiday Worked -Shift3").
In the 2 examples for which I'll give more details, the workday of the technician is 10 hours (because for others it can be 8 hours).
In orange we have the case where "Holiday" in inferior to "Holiday Worked", meaning that the technician worked 11 hours during a public holiday where he should be at rest. In this case, I simply want to delete the row related to "Holiday".
SO if "Holiday" hours (or “Holiday - Shift 2” or “Holiday - Shift 3”) inferior to "Holiday Worked" hours (or "Holiday Worked -Shift2" or "Holiday Worked -Shift3") for the same day and same technician, then delete the entire row corresponding to "Holiday" for the same day and same technician.
In blue we have the case where "Holiday" is superior to "Holiday Worked", meaning that the technician worked 7.75 hours during a public holiday of 10 hours, in which 2.25 hours of rest hours left (10-7.75 = 2.25). In this case I need 2 things please, 1st calculate the difference "Holiday" minus "Holiday Worked". The result (2.25 here) will replace the value of "Holiday" (here replacing 10 by 2.25) to say that this day, the technician had only 2.25 hours of paid rest instead of 10 hours. And 2nd delete the row corresponding to "Holiday Worked" because we don't need it anymore as the worked time was absorbed.
Result:
I hope I was clear enough and don't hesitate to ask if more precisions are needed !
Here are the files if needed : https://file.io/PghHXLxKhuf6
Thanks !
