☰

Excel Powerquery 大约需要 4 小时才能刷新

tag-icon • tag-icon microsoft-excel power-query
Excel Powerquery 大约需要 4 小时才能刷新

我编写了一系列 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 日分别。

查询：

获取命名范围 - 从命名范围获取值。用于原始数据询问。

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

非工作日 - 根据查询返回员工未工作的日期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

函数SumBetween - 计算指定员工编号两个日期之间的病假小时数总和。

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 版本，但它会使你的任务更简单并且更加容易实现。

相关内容