我編寫了一系列 PowerQueries，計算員工連續 12 個月的病假，並考慮到他們的輪班模式（可能不是週一至週五）和合約工時。
它返回生病天數、生病發生次數和布拉德福德評分。
如果有人在週末的兩側生病，則被視為同一事件。
目前，原始資料包含在兩個 CSV 檔案中，共有 180,908 筆記錄。它每週增加約 3100 條記錄 - 這代表每個員工每天的工作時間。
原始資料有 33 列，但我只使用 10 列。
問題是我運行了它，更新花了大約四個小時......太長了。
除了它是 Excel“資料庫”因此無法使用任何查詢折疊這一事實之外，任何人都可以看到任何瓶頸嗎？
任何幫助將不勝感激。
數據示例如下：
創建一些原始資料：
- A1：
={"Date","Facility","Department","Status","Forename","Surname","Emp No","Contract","Sick","Wkg Patt"} - A2：（
=SEQUENCE(438,,44296)格式為日期） - B2：
=IF(A2#<>"",{"Warehouse","Inbound","Perm","D","BC","1",40}) - I2:
=IF(MID(J2,WEEKDAY(A2,16),1)=0,"",IF(RANDBETWEEN(0,50)<10,RANDBETWEEN(1,8),""))- 並填寫 - J2:
=IF(A2#<>"","0011111 " & REPT(CHAR(RANDBETWEEN(65,80)),RANDBETWEEN(0,5)))- 這表示從週六開始的一系列 0 和 1 的輪班模式。末尾的文字顯示此列最初並非用於此目的，因此可能還有其他文字。
將資料保存在兩個 CSV 檔案中（我剛剛在重新儲存之前更改了 B2 公式中的“D”、“BC”和“1”）。
Excel 報表檔案也應包含三個表格（在本例中可以留白）。
Leavers包含兩列 (Emp No和Name)NameUpdates包含兩列 (Emp No和Name)IgnoreList包含三列（Emp No、Start Date和End Date）。
還應該有兩個命名單元格 -StartDate和EndDate。這些細胞目前持有22 年 6 月 17 日和21 年 6 月 18 日分別。
查詢：
fnGetNamedRange - 從指定範圍取得值。內使用原始數據詢問。
let GetNamedRange=(NamedRange) =>
let
name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
value = name{0}[Column1]
in
value
in GetNamedRange
原始數據
此查詢組合了兩個 CSV 文件，刪除Leavers表中列出的所有離開者，更新表中任何拼寫錯誤的名稱NameUpdates，並將表過濾到所需的日期。
let
//Import data from the Reporting Suite.
Source = Csv.Document(File.Contents(
"C:\Documents\Raw Data Suite.csv"
),[Delimiter=",", Columns=33, Encoding=1252, QuoteStyle=QuoteStyle.None]),
RemoveTopRows = Table.Skip(Source,0), //Normally 3, set to 0 for example.
ReportingSuitePromoteHeaders = Table.PromoteHeaders(RemoveTopRows, [PromoteAllScalars=true]),
ReportingSuiteRemoveOtherColumns = Table.SelectColumns(ReportingSuitePromoteHeaders,{"Date", "Facility", "Status", "Forename", "Surname", "Emp No", "Contract", "Department", "Sick", "Wkg Patt"}),
//Import data from the Archive.
ArchiveSource = Csv.Document(File.Contents(
"C:\Documents\Archive.csv"
),[Delimiter=",", Columns=33, Encoding=1252, QuoteStyle=QuoteStyle.None]),
ArchivePromoteHeaders = Table.PromoteHeaders(ArchiveSource, [PromoteAllScalars=true]),
ArchiveRemoveOtherColumns = Table.SelectColumns(ArchivePromoteHeaders,{"Date", "Facility", "Status", "Forename", "Surname", "Emp No", "Contract", "Department", "Sick", "Wkg Patt"}),
//Combine the two tables of data.
CombineTables = Table.Combine({ReportingSuiteRemoveOtherColumns, ArchiveRemoveOtherColumns}),
//Join Forename & Surname.
TrimNames = Table.TransformColumns(CombineTables,{{"Forename", Text.Trim, type text}, {"Surname", Text.Trim, type text}}),
JoinEmployeeName = Table.CombineColumns(TrimNames,{"Forename", "Surname"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Name"),
ChangeNameCase = Table.TransformColumns(JoinEmployeeName,{{"Name", Text.Proper, type text}}),
//Reference Name Updates table.
NameSource = Excel.CurrentWorkbook(){[Name="NameUpdates"]}[Content],
ChangeDataType1 = Table.TransformColumnTypes(NameSource,{{"Emp No", type text}, {"Name", type text}}),
MergeTables = Table.NestedJoin(ChangeNameCase, {"Emp No"}, ChangeDataType1, {"Emp No"}, "NameUpdates", JoinKind.LeftOuter),
ExpandNameUpdates = Table.ExpandTableColumn(MergeTables, "NameUpdates", {"Name"}, {"NameUpdates.Name"}),
AddNewNameColumn = Table.AddColumn(ExpandNameUpdates, "New Name", each if [NameUpdates.Name] = null then [Name] else [NameUpdates.Name], type text),
RemoveOtherColumns = Table.SelectColumns(AddNewNameColumn,{"Date", "Facility", "Status", "New Name", "Emp No", "Contract", "Department", "Sick", "Wkg Patt"}),
RenameColumn = Table.RenameColumns(RemoveOtherColumns,{{"New Name", "Name"}}),
ChangeDataType2 = Table.TransformColumnTypes(RenameColumn,
{{"Date", type date}, {"Facility", type text}, {"Status", type text}, {"Name", type text}, {"Emp No", type text}, {"Contract", type number}, {"Department", type text}, {"Sick", type number}, {"Wkg Patt", type text}}),
//Filter to required dates and select Perm employees who have had a period of sick.
FilterDates = Table.SelectRows(ChangeDataType2, each [Date] >= Date.From(fnGetNamedRange("StartDate")) and [Date] <= Date.From(fnGetNamedRange("EndDate"))),
FilterRows = Table.SelectRows(FilterDates, each ([Status] = "Perm")),
//Reference the Leavers table.
LeaversSource = Excel.CurrentWorkbook(){[Name="Leavers"]}[Content],
RemovedName = Table.SelectColumns(LeaversSource,{"Emp No"}),
ChangeDataType3 = Table.TransformColumnTypes(RemovedName,{{"Emp No", type text}}),
RemoveLeavers = Table.NestedJoin(FilterRows, {"Emp No"}, ChangeDataType3, {"Emp No"}, "Leavers", JoinKind.LeftAnti),
RemoveLeaverColumn = Table.RemoveColumns(RemoveLeavers,{"Leavers"}),
SortTable = Table.Sort(RemoveLeaverColumn,{{"Emp No", Order.Ascending}, {"Date", Order.Ascending}}),
//Indicate which days are work days and which aren't.
GetShiftPattern = Table.AddColumn(SortTable, "Shift Pattern", each Text.Start(Text.From([Wkg Patt], "en-GB"), 7), type text),
IsShiftPattern = Table.AddColumn(GetShiftPattern, "ShiftPatternCheck", each Value.Is(Value.FromText(Text.Start(Text.From([Wkg Patt], "en-GB"), 7)), type number)),
IsWorkDay = Table.AddColumn(IsShiftPattern, "WorkDay", each if [ShiftPatternCheck]=true then Text.Middle([Shift Pattern], Date.DayOfWeek([Date], Day.Saturday), 1) else
if Date.DayOfWeek([Date],Day.Saturday)<=1 then 0 else 1, type text),
ChangeDataType4 = Table.TransformColumnTypes(IsWorkDay,{{"WorkDay", Int64.Type}}),
RemoveShiftPatternColumns = Table.Buffer(Table.RemoveColumns(ChangeDataType4,{"Wkg Patt", "Shift Pattern", "ShiftPatternCheck"})),
//Combine Departments
RemoveOtherColumns1 = Table.SelectColumns(RemoveShiftPatternColumns,{"Emp No", "Department"}),
RemoveDuplicates1 = Table.Distinct(RemoveOtherColumns1),
GroupDepartments = Table.Group(RemoveDuplicates1, {"Emp No"}, {{"Department", each Text.Combine([Department], ", "), type nullable text}}),
AddDepartments = Table.NestedJoin(RemoveShiftPatternColumns, {"Emp No"}, GroupDepartments, {"Emp No"}, "Dept", JoinKind.LeftOuter),
ExpandDepartments = Table.ExpandTableColumn(AddDepartments, "Dept", {"Department"}, {"Department.1"}),
RemoveOtherColumns2 = Table.SelectColumns(ExpandDepartments,{"Date", "Facility", "Status", "Name", "Emp No", "Contract", "Department.1", "Sick", "WorkDay"}),
RenameColumns = Table.Buffer(Table.RenameColumns(RemoveOtherColumns2,{{"Department.1", "Department"}}))
in
RenameColumns
非工作日- 根據 Wkg Patt 列中的 0 和 1 列出每位員工的休息日。
let
Source = #"Raw Data",
RemoveWorkingDays = Table.SelectRows(Source, each ([WorkDay] = 0)),
RemoveOtherColumns = Table.Buffer(Table.SelectColumns(RemoveWorkingDays,{"Date", "Emp No"}))
in
RemoveOtherColumns
網路日- 計算不包括假期（或本例中的休息日）的工作日數
let
func = (StartDate as date, EndDate as date, optional Holidays as list, optional StartOfWeek as number) =>
let
// optional StartOfWeek, if empty the week will start on Monday
startOfWeek = if StartOfWeek = null then 1 else StartOfWeek,
// Providing for logic where EndDate is after StartDate
Start = List.Min({StartDate, EndDate}),
End = List.Max({StartDate, EndDate}),
// Switch sign if EndDate is before StartDate
Sign = if EndDate < StartDate then -1 else 1,
// Get list of dates between Start- and EndDate
ListOfDates = List.Dates(Start, Number.From(End-Start)+1,#duration(1,0,0,0)),
// if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
// otherwise continue with previous table
DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )),
// Count the number of days (items in the list)
CountDays = List.Count(DeleteHolidays) * Sign
in
CountDays
in
func
fn非工作日 - 根據查詢傳回員工未工作的日期Non-work days。
let
non_work_dates = (SourceTable as table, StartDate as date, EndDate as date, EmpNo as text) =>
let
Source = SourceTable,
FilterToEmpNo = Table.SelectRows(Source, each [Emp No] = EmpNo and [Date] >= StartDate and [Date] <= EndDate),
RemoveEmpNo = Table.SelectColumns(FilterToEmpNo,{"Date"}),
ToList = RemoveEmpNo[Date]
in
ToList
in
non_work_dates
生病時期- 確定每個疾病期間的開始和結束日期
let
Source = #"Raw Data",
FilterToSick = Table.SelectRows(Source, each ([Sick] <> null)),
//The data is sorted by Employee Number and Date so periods of sickness per employee appear next to each other.
//Add three indexes so data from the previous and next row may be referenced in the current row.
//Index1 starts at 1.
AddIndex1 = Table.AddIndexColumn(FilterToSick, "Index1", 0, 1, Int64.Type),
Index1PK = Table.AddKey(AddIndex1, {"Index1"}, true),
SortByIndex1 = Table.Sort(Index1PK, {"Index1", Order.Ascending}),
//We're only adding the Date & Emp No from the previous & next row to the current row.
//So a table is created containing only those two fields.
//Add Index2 to the JoinTable. This will start at 0
JoinTable = Table.SelectColumns(Index1PK,{"Date", "Emp No"}),
AddIndex2 = Table.AddIndexColumn(JoinTable, "Index2", 1, 1, Int64.Type),
Index2PK = Table.AddKey(AddIndex2, {"Index2"}, true),
SortByIndex2PK = Table.Sort(Index2PK, {"Index2", Order.Ascending}),
//Add Index3 to the JoinTable. This will start at 2
AddIndex3 = Table.AddIndexColumn(JoinTable, "Index3", -1, 1, Int64.Type),
Index3PK = Table.AddKey(AddIndex3, {"Index3"}, true),
SortByIndex3PK = Table.Sort(Index3PK, {"Index3", Order.Ascending}),
//Add the Employee Number and Date from the next row to the current row.
Index2Merge = Table.Join(Table.PrefixColumns(SortByIndex2PK, "Prev"), {"Prev.Index2"}, Table.PrefixColumns(SortByIndex1,"Current"), {"Current.Index1"}, JoinAlgorithm.SortMerge),
SortByDate1 = Table.Sort(Index2Merge,{{"Current.Emp No", Order.Ascending}, {"Current.Date", Order.Ascending}}),
Index3Merge = Table.Join(Table.PrefixColumns(SortByIndex3PK,"Next"), {"Next.Index3"}, SortByDate1, {"Current.Index1"}, JoinAlgorithm.SortMerge),
SortByDate1Again = Table.Sort(Index3Merge,{{"Current.Emp No", Order.Ascending}, {"Current.Date", Order.Ascending}}),
DaysSincePreviousSick = Table.AddColumn(SortByDate1Again, "Days Since Previous", each try fnNetworkDays([Prev.Date],[Current.Date],fnNONWORKDAYS(#"Non-work days",[Prev.Date], [Current.Date], [Current.Emp No]))-1 otherwise null),
DaysToNextSick = Table.AddColumn(DaysSincePreviousSick, "Days To Next", each try fnNetworkDays([Current.Date],[Next.Date],fnNONWORKDAYS(#"Non-work days",[Current.Date], [Next.Date], [Current.Emp No]))-1 otherwise null),
IdentifyStart = Table.AddColumn(DaysToNextSick, "Start", each if [Days Since Previous]<>1 or [Prev.Date]=null or [Current.Emp No]<>[Prev.Emp No] then "Start" else null),
IdentifyEnd = Table.AddColumn(IdentifyStart, "End", each if [Days To Next]<>1 or [Current.Emp No]<>[Next.Emp No] then "End" else null),
RemoveOtherColumns = Table.SelectColumns(IdentifyEnd,{"Current.Date", "Current.Facility", "Current.Status", "Current.Name", "Current.Emp No", "Current.Contract", "Current.Department", "Current.Sick", "Start", "End"}),
RenameColumns= Table.RenameColumns(RemoveOtherColumns,{{"Current.Date", "Date"}, {"Current.Facility", "Facility"}, {"Current.Status", "Status"}, {"Current.Name", "Name"},
{"Current.Emp No", "Emp No"}, {"Current.Contract", "Contract"}, {"Current.Department", "Department"}, {"Current.Sick", "Sick"}})
in
RenameColumns
fnSumBetween - 計算指定員工人數的兩個日期之間的病假時間。
let
Sum_Between = (SourceTable as table, StartDate as date, EndDate as date, EmpNo as text) =>
let
Source = SourceTable,
FilterToData = Table.SelectRows(Source, each [Emp No] = EmpNo and [Date] >= StartDate and [Date] <= EndDate),
GroupRows = Table.Group(FilterToData, {"Emp No"}, {{"Sick Hours", each List.Sum([Sick]), type nullable number}})
in
GroupRows
in
Sum_Between
總生病時間 - 傳回每個病假期間的單行以及病假天數。
let
Source = #"Periods of Sick",
MergeStartAndEnd = Table.CombineColumns(Source,{"Start", "End"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"SickPeriods"),
//Add multi-day sick.
FilterRows1 = Table.SelectRows(MergeStartAndEnd, each ([SickPeriods] = "End" or [SickPeriods] = "Start")),
AddIndex1 = Table.AddIndexColumn(FilterRows1, "Index1", 0, 1, Int64.Type),
AddIndex2 = Table.AddIndexColumn(AddIndex1, "Index2", 1, 1, Int64.Type),
MergeIndexes = Table.Join(AddIndex2, "Index1", Table.PrefixColumns(AddIndex2,"1"), "1.Index2", JoinAlgorithm.SortMerge),
FilterRows2 = Table.SelectRows(MergeIndexes, each ([SickPeriods] = "End")),
RemoveOtherColumns1 = Table.SelectColumns(FilterRows2,
{"1.Date", "Date", "Facility", "Status", "Name", "Emp No", "Contract", "Department"}),
RenameColumns1 = Table.RenameColumns(RemoveOtherColumns1,{{"1.Date", "From"}, {"Date", "To"}}),
AddSickHours = Table.AddColumn(RenameColumns1, "Sick", each fnSumBetween(#"Periods of Sick",[From],[To],[Emp No])),
ExpandTable = Table.ExpandTableColumn(AddSickHours, "Sick", {"Sick Hours"}, {"Sick Hours"}),
//Add single day sick periods.
FilterRows3 = Table.SelectRows(MergeStartAndEnd, each ([SickPeriods] = "StartEnd")),
DuplicateDateColumn = Table.DuplicateColumn(FilterRows3, "Date", "To"),
RemoveOtherColumns2 = Table.SelectColumns(DuplicateDateColumn,{"Date", "To", "Facility", "Status", "Name", "Emp No", "Contract", "Department", "Sick"}),
RenameColumns2 = Table.RenameColumns(RemoveOtherColumns2,{{"Date", "From"}, {"Sick", "Sick Hours"}}),
//Join single and multi day sick periods.
AppendData = Table.Combine({ExpandTable, RenameColumns2}),
SortRows = Table.Sort(AppendData,{{"Emp No", Order.Ascending}, {"From", Order.Ascending}}),
CalculateDaysSick = Table.AddColumn(SortRows, "Days Sick", each Number.Round([Sick Hours]/([Contract]/5),2))
in
CalculateDaysSick
布拉德福德分數 - 回傳生病的總天數和發生次數以及布拉德福德分數
let
Source = #"Total Periods of Sick",
GroupRows = Table.Group(Source, {"Emp No", "Name", "Department", "Facility"}, {{"Total Days", each List.Sum([Days Sick]), type number}, {"Occurrences", each Table.RowCount(_), Int64.Type}}),
AddBradfordScore = Table.AddColumn(GroupRows, "Bradford Score", each Number.Power([Occurrences],2)*[Total Days])
in
AddBradfordScore
答案1
您使用了錯誤的工具，就像試圖使用金匠的錘子敲擊鐵路道釘一樣。有許多可用的關係資料庫，包括免費的自由辦公室 根據， 和好多其它的。為了根據，以及許多其他資料庫，CSV 檔案可以匯入到新資料庫中只需很少的努力。
可能需要一段時間才能學習資料庫查詢語言的基礎知識，例如 SQL 的一個版本，但它會讓你的任務簡單得多並且更容易實現。