Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I hope I was clear enough and don't hesitate to ask if more precisions are needed!
You will find the files here : Timesheet
Solved! Go to Solution.
Hi @ilyes4205, 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
Hi @ilyes4205, 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
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 !
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.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.