Multiple operations (Power Query)

[ilyes4205]

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 :

• 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”).

The 3rd column (named "Work Date") gives the date related to the hours, the 4th column ("Hours") gives the number of hours spent and the 5th column ("Week No") gives the week number of the related week.

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!

Files are attached.

Thanks !

 

[ilyes4205]

To figure out a solution quicker I also posted here : https://www.mrexcel.com/board/threads/multiples-operations-through-power-query.1255949/#post-6167352

 

[GraH - Guido]

Hi,

You can do a group by on work date and technician name, and use aggregate All Rows. That gives you a sub table to work with.
Now you can apply some logics on that table.
-> Table.FindText(AllRows, "Holiday Worked"), will give you a record
-> Table.FindText(AllRows, "Holiday - "), will give you a record // Or you can try a Table.SelectRows(AllRows, each Text.StartsWith([PayCode], "Holiday -"))
-> ...

From the records you can extract the field [Hours] and compare.
If it fits your criteria you can do a Table.SelectRows(AllRow, each [Pay Code] <> "Holiday")

For all other use cases you can reconstruct the record and apply the calculation.

Best is to convert these steps into a function.

 

[GraH - Guido]

ilyes4205,

See if this works for you. From the queries and connections pane, you can copy and paste the query into the Power BI desktop in the Power Query UI. The helper list I created, will be copied too as it is used in one of the query steps.
Just change the original path again to yours in the Source step.

I can't guarantee it will work fast with much more files.
The code can be simplified, but I do not have the time any more to work on it.

let
    Source = Folder.Files("C:\Users\Installatie\Desktop\timesheet v1\2023"),
    Filter_files = Table.SelectRows(Source, each not Text.StartsWith([Name], "~")), // I have a temp file in here, do not know where it came from
    Workbook = Table.TransformColumns(Filter_files,{{"Content", Excel.Workbook, type table}}),
    Keep_content = Table.SelectColumns(Workbook,{"Content"}),
    Expand_data_col = Table.ExpandTableColumn(Keep_content, "Content", {"Data"}, {"Data"})[Data],
    Data_all = Table.Combine(Expand_data_col),
    Data_cols = Table.ToColumns(Data_all), // creates a list of lists, each inner list a whole column
    Data_no_null_cols = List.Select(Data_cols, each List.NonNullCount(_)>0),
    Data_rows = Table.ToRows(Table.FromColumns(Data_no_null_cols)), // creates a list of lists, each inner list a whole row
    Data_no_null_rows = List.Select(Data_rows, each List.NonNullCount(_)>0),
    Data_as_tbl = Table.FromRows(Data_no_null_rows),
    Fill_down = Table.FillDown(Data_as_tbl,{"Column3", "Column4", "Column6", "Column8", "Column16"}),
    Skip_top_rows_2 = Table.Skip(Fill_down,2),
    Promote_headers = Table.PromoteHeaders(Skip_top_rows_2, [PromoteAllScalars=true]),
    Select_cols = Table.SelectColumns(Promote_headers,{"Employee Name", "Pay Code", "Work Date", "Hours"}),
    No_repeat_headers = Table.SelectRows(Select_cols, each ([Employee Name] <> "Employee Name")),
    Filter_pay_code_not_null = Table.SelectRows(No_repeat_headers, each ([Pay Code] <> null)),
    Set_types = Table.TransformColumnTypes(Filter_pay_code_not_null,{{"Employee Name", type text}, {"Pay Code", type text}, {"Work Date", type text},  {"Hours", type number}}),
    Add_PCG = Table.AddColumn(Set_types, "PCG", each List.ReplaceMatchingItems({[Pay Code]},PayCodes_grp){0}, type text),
    Group_Hours = Table.Group(Add_PCG, {"Employee Name", "Pay Code", "Work Date", "PCG"}, {{"Hours", each List.Sum([Hours]), type nullable number}}),
    Group_all = Table.Group(Group_Hours, {"Employee Name", "Work Date"}, {{"All", each _, type table [Employee Name=nullable text, Pay Code=nullable text, Work Date=nullable text, Time In=nullable time, Time Out=nullable time, Hours=nullable number]}}),
    Add_calc = Table.AddColumn(Group_all, "CalcTabl", each [
    IsRecalc = List.AllTrue(
        {
        List.Contains([All][PCG], "OFF"),
        List.Contains([All][PCG], "Holiday Worked")
        }),
    OffRows = Table.SelectRows([All], each [PCG] = "OFF"),
    OffTme = OffRows[Hours]{0},
    WorkedRows = Table.SelectRows([All], each [PCG] = "Holiday Worked"),
    WorkTme = WorkedRows[Hours]{0},
    NewOffVal = OffTme - WorkTme,
    Newtbl = if not IsRecalc then [All] else
             if NewOffVal > 0 then Table.SelectRows(Table.ReplaceValue([All], OffTme, NewOffVal, Replacer.ReplaceValue, {"Hours"}), each [PCG] <> "Holiday Worked")
             else
                 Table.SelectRows([All], each [PCG] <> "OFF")
][Newtbl], type table)[CalcTabl],
    Combine_All = Table.Combine(Add_calc)
in
    Combine_All