Forum Discussion
Help for operations (Power Query)
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...
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
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
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 !
You're welcome. If you want to change the logic, you can delete equal sign here:
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.
You can use temporary filter at the beginning of the query. How many rows do you have?