Help for operations (Power Query)

[Anonymous]

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 datewhen 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!
  
  You will find the files here : Timesheet 

   Thanks.
  

  Attachments

[dufoq3 · Accepted solution]

Hi @Anonymous, I've edited whole query you've provided in pbix file.

Result

let
Source = Excel.Workbook(File.Contents("C:\Users\krifaily\Desktop\timesheet v1\2023\Report_STD_EMPDETAIL_TIMESHEET_2023-11-27.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    RemovedBlankColumns = Table.FromColumns(List.Select(Table.ToColumns(Sheet1_Sheet), each List.Count(List.RemoveNulls(_)) > 0)),
    RemovedBlankRows = Table.SelectRows(RemovedBlankColumns, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    RemovedTopRows = Table.Skip(RemovedBlankRows, each [Column3] <> "Employee Name"),
    PromotedHeaders = Table.PromoteHeaders(RemovedTopRows, [PromoteAllScalars=true]),
    ColumnNamesUpdated = List.Transform(Table.ColumnNames(PromotedHeaders), each if Text.StartsWith(_, "Column") then try Text.Replace(_, _, Table.Column(PromotedHeaders, _){0}?) otherwise _ else _),
    RenamedColumns = Table.RenameColumns(PromotedHeaders, List.Zip({ Table.ColumnNames(PromotedHeaders), ColumnNamesUpdated })),
    RemovedTopRows2 = Table.Skip(RenamedColumns, each [Employee Name] = null),
    RemovedOtherColumns = Table.SelectColumns(RemovedTopRows2,{"Employee Name", "Pay Code", "Work Date", "Hours"}),
    FilledDown = Table.FillDown(RemovedOtherColumns,{"Employee Name"}),
    FilteredRows = Table.SelectRows(FilledDown, each ([Pay Code] <> null and [Pay Code] <> "Meal")),
    ChangedType = Table.TransformColumnTypes(FilteredRows,{{"Employee Name", type text}, {"Pay Code", type text}, {"Work Date", type date}, {"Hours", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Employee Name", "Pay Code", "Work Date"}, {{"Hours", each List.Sum([Hours]), type nullable number}}),
    Ad_PayCodeHelper = Table.AddColumn(GroupedRows, "Pay Code Helper", each if Text.StartsWith([Pay Code], "Holiday", Comparer.OrdinalIgnoreCase) then "Holiday" else [Pay Code], type text),
    GroupedRows2 = Table.Group(Ad_PayCodeHelper, {"Employee Name", "Work Date", "Pay Code Helper"}, {{"Hours", each 
        [ a = Table.SelectRows(_, (x)=> Text.Contains(x[Pay Code], "Worked", Comparer.OrdinalIgnoreCase)),     //Holiday Worked Hours
          b = Table.SelectRows(_, (x)=> not Text.Contains(x[Pay Code], "Worked", Comparer.OrdinalIgnoreCase)), //Holiday Shift Hours
          c = try if a[Hours]{0}? >= b[Hours]{0}? then Record.SelectFields(a{0}, {"Pay Code", "Hours"}) else [Pay Code = b[Pay Code]{0}, Hours = b[Hours]{0}?-a[Hours]{0}?] otherwise [Pay Code = [Pay Code]{0}, Hours = List.Sum([Hours])]
        ][c], type record}}),
    RemovedColumns = Table.RemoveColumns(GroupedRows2,{"Pay Code Helper"}),
    ExpandedRecord = Table.ExpandRecordColumn(RemovedColumns, "Hours", {"Pay Code", "Hours"}),
    Ad_WeekOfYear = Table.AddColumn(ExpandedRecord, "Week of Year", each Date.WeekOfYear([Work Date], Day.Monday), Int64.Type)
in
    Ad_WeekOfYear

[Anonymous]

Hi man it works amazingly ! Thanks. I have forgot to precise one thing in my request. 

In the orange case where "Holiday" is inferior than "Holiday Worked", I simply want to delete the row related to "Holiday". I forgot to precise inferior or equal !

[dufoq3]

You're welcome. If you want to change the logic, you can delete equal sign here:

[Anonymous]

Ok thanks perfect. I adapted your code to my needs and everything should be fine. I am working on that since this morning but it is slow as hell. Is there any way to refresh faster the preview ? Because I always need to wait a loooong time at each steps to see if what I adapted is fine or not.

[dufoq3]

You can use temporary filter at the beginning of the query. How many rows do you have? 

[wdx223_Daniel]

let
Source = Excel.Workbook(File.Contents("C:\Users\krifaily\Desktop\timesheet v1\2023\Report_STD_EMPDETAIL_TIMESHEET_2023-11-27.xlsx""), null, true)[Data]{0},
Custom1 = Table.FromColumns(List.Select(List.Skip(Table.ToColumns(Source),2),each List.RemoveNulls(_)<>{})),
Custom2 = Table.PromoteHeaders(Table.Skip(Table.FromRows(List.Select(Table.ToRows(Custom1),each List.RemoveNulls(_)<>{})),1)),
Custom3 = Table.FillDown(Table.SelectColumns(Custom2,{"Column1","Pay Code","Work Date","Hours"}),{"Column1"}),
Custom4 = Table.SelectRows(Table.SelectColumns(Custom3,{"Column1","Pay Code","Work Date","Hours"}),each [Pay Code]<>null and [Pay Code]<>"Meal"),
Custom5 = Table.Group(Custom4,{"Column1","Pay Code","Work Date"},{"Hours",each (if Text.StartsWith([Pay Code]{0},"Holiday Worked") then -1 else 1)*List.Sum([Hours])}),
#"Changed Type" = Table.TransformColumnTypes(Custom5,{{"Column1", type text}, {"Work Date", type date}, {"Pay Code", type text}, {"Hours", type number}}),
Custom6 = Table.Group(#"Changed Type",{"Column1","Work Date"},{"n",each Table.Group(_,"Pay Code",{"Hours",each List.Sum([Hours])},1,(x,y)=>let fx=(t)=>if Text.StartsWith(t,"Holiday") then Text.Replace(t," Worked","") else t in Value.Compare(fx(x),fx(y)))}),
Custom7 = Table.ExpandTableColumn(Custom6,"n",{"Pay Code","Hours"}),
Custom8 = Table.SelectRows(Custom7,each [Hours]>=0),
Custom9 = Table.RenameColumns(Custom8,{"Column1","Technician Name"}),
Custom10 = Table.AddColumn(Custom9,"Week No",each Date.WeekOfYear([Work Date],Day.Monday))
in
Custom10