私は、シフトパターン (月曜から金曜ではない場合があります) と契約時間を考慮して、従業員の 12 か月間の病欠を計算する一連の PowerQueries を作成しました。
病欠日数、病欠の発生回数、および Bradford スコアを返します。
週末の前後に病欠した場合、同じ発生としてカウントされます。
現時点では、生データは 2 つの CSV ファイルに 180,908 レコードで含まれています。これは 1 週間あたり約 3100 レコードずつ増加します。これは、従業員 1 人あたりの 1 日の労働時間を表します。
元のデータには 33 列ありますが、使用するのは 10 列だけです。その他の列は、クエリの最初の手順の 1 つとして削除されます。
問題実行してみたところ、更新に約 4 時間かかりました。長すぎます。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}) - 私2:
=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 のシフト パターンを表します。末尾のテキストは、この列が元々このために意図されたものではなかったことを示しています。そのため、他のテキストも含まれている可能性があります。
2 つの CSV ファイルにわたってデータを保存します (再保存する前に、B2 の数式で「D」、「BC」、および「1」を変更しました)。
Excel レポート ファイルには 3 つのテーブルも含める必要があります (例では空のままにしておくことができます)。
Leavers2つの列（Emp NoおよびName）を含むNameUpdates2つの列（Emp NoおよびName）を含むIgnoreList3 つの列 (Emp No、Start DateおよびEnd Date) が含まれます。
また、名前の付いたセルが2つあるはずです -StartDateとEndDate。これらのセルには現在、2022年6月17日そして2021年6月18日それぞれ。
クエリ:
fnGetNamedRange - 名前付き範囲から値を取得します。生データクエリ。
let GetNamedRange=(NamedRange) =>
let
name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
value = name{0}[Column1]
in
value
in GetNamedRange
生データ
このクエリは、2 つの 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
fnネットワーク日数- 休日（この場合は休日）を除いた営業日数を計算します
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
fn合計 - 指定された従業員番号の 2 つの日付間の病欠時間を合計します。
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
病欠期間合計 - 病気期間ごとに病気日数とともに 1 行を返します。
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のバージョンなどですが、タスクがはるかにシンプルより達成可能になります。