Обновление Excel Powerquery занимает около 4 часов

2024-11-2 • tag-icon

Обновление Excel Powerquery занимает около 4 часов

Я написал ряд PowerQuery, которые вычисляют 12-месячную продолжительность болезни сотрудников, принимая во внимание график их смен (может не быть пн-пт) и их контрактные часы.
Он возвращает количество дней болезни, количество случаев болезни и индекс Брэдфорда.
Если кто-то болеет по обе стороны от выходных, это считается одним и тем же случаем.

На данный момент необработанные данные содержатся в двух файлах CSV по 180 908 записей. Они растут примерно на 3100 записей в неделю — это часы, отработанные одним сотрудником в день.
Исходные данные имеют 33 столбца, но я использую только 10. Остальные столбцы удаляются на одном из первых шагов в запросах.

Проблемав том, что я запустил его, и обновление заняло около четырех часов... слишком долго.
Может ли кто-нибудь увидеть какие-либо узкие места, помимо того, что это "база данных" Excel, поэтому нельзя использовать свертывание запросов.
Любая помощь будет высоко оценена.

Пример данных:

Создайте некоторые необработанные данные:

А1:={"Date","Facility","Department","Status","Forename","Surname","Emp No","Contract","Sick","Wkg Patt"}
А2: =SEQUENCE(438,,44296)(формат как дата)
Би 2:=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, начинающейся в субботу. Текст в конце показывает, что этот столбец изначально не был предназначен для этого, поэтому может иметь и другой текст.

Сохраните данные в двух файлах CSV (я просто изменил «D», «BC» и «1» в формуле B2 перед повторным сохранением).

Файл отчета Excel также должен содержать три таблицы (которые для примера можно оставить пустыми).

Leaversсодержащий два столбца ( Emp Noи Name)
NameUpdatesсодержащий два столбца ( Emp Noи Name)
IgnoreListсодержащий три столбца ( Emp No, Start Dateи End Date).

Также должно быть две именованные ячейки - StartDateи EndDate. Эти ячейки в настоящее время содержат17-июн-22и18-июн-21соответственно.

Запросы:

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

Нерабочие дни- перечисляет выходные дни каждого сотрудника на основе значений нулей и единиц в столбце «Патент недели».

let
    Source = #"Raw Data",
    RemoveWorkingDays = Table.SelectRows(Source, each ([WorkDay] = 0)),
    RemoveOtherColumns = Table.Buffer(Table.SelectColumns(RemoveWorkingDays,{"Date", "Emp No"}))
in
    RemoveOtherColumns

fnNetworkDays- рассчитывает количество рабочих дней без учета праздников (или выходных в данном случае)

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

Вы используете неправильный инструмент, как будто пытаетесь использовать молоток ювелира, чтобы забить железнодорожный костыль. Естьмногодоступны реляционные базы данных, в том числе бесплатныеLibreOffice База, имногие другие. ДляБаза, и многие другие базы данных,Файлы CSV можно импортировать в новую базу данныхс небольшими усилиями.

Это может занять некоторое время.изучить основы языка запросов к базе данных, например, версию SQL, но это усложнит вашу задачугораздо прощеи более достижимыми.

Создайте некоторые необработанные данные:

Запросы:

решение1

Связанный контент