let
Источник = Folder.Files("\\tsclient\S\Рабочие группы\HR Аналитика_исходник\CRM РАБОТУТ"),
#"Отфильтрованные скрытые файлы1" = Table.SelectRows(Источник, each [Attributes]?[Hidden]? <> true),
#"Вызвать настраиваемую функцию1" = Table.AddColumn(#"Отфильтрованные скрытые файлы1", "Преобразовать файл", each #"Преобразовать файл"([Content])),
#"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
#"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать файл"}),
#"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать файл", Table.ColumnNames(#"Преобразовать файл"(#"Пример файла"))),
#"Измененный тип" = Table.TransformColumnTypes(#"Столбец расширенной таблицы1",{{"Source.Name", type text}, {"Этап", Int64.Type}, {"Дата и время поступления отклика(МСК)", type datetime}, {"Дата и время поступления отклика(Менеджер)", type datetime}, {"ID вакансии в CRM RBT", Int64.Type}, {"Статус", type text}, {"ФИО МПП вакансии", type text}, {"Департамент МПП вакансии", type text}, {"Адрес вакансии", type text}, {"Торговая точка", type text}, {"Должность", type text}, {"ФИО кандидата", type text}, {"Источник отклика", type text}, {"Менеджер который обрабатывал отклик", type text}, {"Причина закрытия отклика", type text}, {"Департамент менеджера, который обрабатывал отклик", type text}, {"Дата и время взятия отклика в работу", type datetime}, {"Дата и время окончания работы с откликом", type datetime}, {"Статус кандидата после обработки отклика", type text}, {"ID вакансии в CRM RBT, на которую направлен кандидат", Int64.Type}, {"UTM", type text}, {"ID", type text}, {"Click ID", type text}, {"Телефон", type text}, {"ID родительского отклика", type text}}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Измененный тип", "Сегодня", each DateTime.FromText(Text.From(DateTime.Date(DateTime.LocalNow())) & " 09:30:00")),
#"Измененный тип1" = Table.TransformColumnTypes(#"Добавлен пользовательский объект",{{"Сегодня", type datetime}}),
#"Замененное значение" = Table.ReplaceValue(#"Измененный тип1",null,"Не отработан отклик",Replacer.ReplaceValue,{"Статус кандидата после обработки отклика"}),
#"Условный столбец добавлен" = Table.AddColumn(#"Замененное значение", "БН", each if [#"Департамент менеджера, который обрабатывал отклик"] = null then [Департамент МПП вакансии] else [#"Департамент менеджера, который обрабатывал отклик"]),
#"Объединенные запросы" = Table.NestedJoin(#"Условный столбец добавлен", {"Статус кандидата после обработки отклика"}, Критерии, {"Статус кандидата выгрузка"}, "Критерии", JoinKind.LeftOuter),
#"Развернутый элемент Критерии" = Table.ExpandTableColumn(#"Объединенные запросы", "Критерии", {"Критерий", "Фактор"}, {"Критерий", "Фактор"}),
#"Сортированные строки" = Table.Sort(#"Развернутый элемент Критерии",{{"ID", Order.Ascending}}),
#"Объединенные запросы1" = Table.NestedJoin(#"Сортированные строки", {"ID"}, Этап_итог, {"ID"}, "Этап_итог", JoinKind.LeftOuter),
#"Развернутый элемент Этап_итог" = Table.ExpandTableColumn(#"Объединенные запросы1", "Этап_итог", {"Этап"}, {"Этап_итог"}),
#"Добавлен пользовательский объект2" = Table.AddColumn(#"Развернутый элемент Этап_итог", "Разница t с Мск", each [#"Дата и время поступления отклика(Менеджер)"]-[#"Дата и время поступления отклика(МСК)"]),
#"Условный столбец добавлен1" = Table.AddColumn(#"Добавлен пользовательский объект2", "Итог_Дата и время взятия отклика в работу", each if [Дата и время взятия отклика в работу] = null then [Сегодня] else [Дата и время взятия отклика в работу]),
#"Условный столбец добавлен2" = Table.AddColumn(#"Условный столбец добавлен1", "Итог_Дата и время окончания работы с откликом", each if [Дата и время окончания работы с откликом] = null then [Сегодня] else [Дата и время окончания работы с откликом]),
#"Добавлен пользовательский объект1" = Table.AddColumn(#"Условный столбец добавлен2", "Дата и время взятия отклика в работу(Менеджер)", each if [Дата и время взятия отклика в работу] = "null" then [Сегодня] else [Итог_Дата и время взятия отклика в работу]+[Разница t с Мск]),
#"Добавлен пользовательский объект3" = Table.AddColumn(#"Добавлен пользовательский объект1", "Дата и время окончания работы с откликом(Менеджер)", each if [Дата и время окончания работы с откликом] = "null" then [Сегодня] else [Итог_Дата и время окончания работы с откликом]+[Разница t с Мск]),
#"Дублированный столбец" = Table.DuplicateColumn(#"Добавлен пользовательский объект3", "Дата и время поступления отклика(Менеджер)", "Копия Дата и время поступления отклика(Менеджер)"),
#"Разделить столбец по разделителю" = Table.SplitColumn(Table.TransformColumnTypes(#"Дублированный столбец", {{"Копия Дата и время поступления отклика(Менеджер)", type text}}, "ru-RU"), "Копия Дата и время поступления отклика(Менеджер)", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Дата поступления отклика(Менеджер)", "Время поступления отклика(Менеджер)"}),
#"Измененный тип2" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Дата поступления отклика(Менеджер)", type date}, {"Время поступления отклика(Менеджер)", type time}}),
#"Объединенные запросы2" = Table.NestedJoin(#"Измененный тип2", {"Дата поступления отклика(Менеджер)"}, Календарь, {"Дата"}, "Календарь", JoinKind.LeftOuter),
#"Развернутый элемент Календарь" = Table.ExpandTableColumn(#"Объединенные запросы2", "Календарь", {"День", "Время начала", "Время окончания (для отклика)", "Время окончания ", "Перенести"}, {"День", "Время начала", "Время окончания (для отклика)", "Время окончания ", "Перенести"}),
#"Условный столбец добавлен3" = Table.AddColumn(#"Развернутый элемент Календарь", "Итог_Дата поступления отклика(Менеджер)", each if [День] = "Выходной" then [Перенести] else if [День] = "Праздничный" then [Перенести] else if [#"Время поступления отклика(Менеджер)"] >= [#"Время окончания (для отклика)"] then [Перенести] else [#"Дата поступления отклика(Менеджер)"]),
#"Условный столбец добавлен4" = Table.AddColumn(#"Условный столбец добавлен3", "Итог_время поступления отклика(Менеджер)", each if [День] = "Выходной" then #time(9, 30, 0) else if [День] = "Праздничный" then #time(9, 30, 0) else if [#"Время поступления отклика(Менеджер)"] >= [#"Время окончания (для отклика)"] then #time(9, 30, 0) else if [#"Время поступления отклика(Менеджер)"] < #time(9, 30, 0) then #time(9, 30, 0) else [#"Время поступления отклика(Менеджер)"]),
#"Объединенные столбцы" = Table.CombineColumns(Table.TransformColumnTypes(#"Условный столбец добавлен4", {{"Итог_Дата поступления отклика(Менеджер)", type text}, {"Итог_время поступления отклика(Менеджер)", type text}}, "ru-RU"),{"Итог_Дата поступления отклика(Менеджер)", "Итог_время поступления отклика(Менеджер)"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Итог_Дата и время поступления отклика(Менеджер)"),
#"Измененный тип3" = Table.TransformColumnTypes(#"Объединенные столбцы",{{"Итог_Дата и время поступления отклика(Менеджер)", type datetime}, {"Итог_Дата и время окончания работы с откликом", type datetime}, {"Дата и время взятия отклика в работу(Менеджер)", type datetime}, {"Дата и время окончания работы с откликом(Менеджер)", type datetime}, {"Итог_Дата и время взятия отклика в работу", type datetime}}),
#"Добавлен пользовательский объект4" = Table.AddColumn(#"Измененный тип3", "Скорость взятия отклика(МПП)", each let
StartDateTime = [#"Итог_Дата и время поступления отклика(Менеджер)"],
EndDateTime = [#"Дата и время взятия отклика в работу(Менеджер)"],
WorkStartTime = Time.From([Время начала]),
WorkEndTime = Time.From([#"Время окончания "]),
HolidayList = Holidays[HolidayDate],
StartDate = Date.From(StartDateTime),
EndDate = Date.From(EndDateTime),
// генерация списка всех дат между началом и концом
AllDates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),
// отбор только рабочих дней
WorkingDays = List.Select(AllDates, each IsWorkingDay(HolidayList, _)),
// расчёт рабочего времени:
WorkingDuration = List.Sum(List.Transform(WorkingDays, each
let
currentDay = _,
startTime = if currentDay = StartDate then Time.From(StartDateTime) else WorkStartTime,
endTime = if currentDay = EndDate then Time.From(EndDateTime) else WorkEndTime,
duration = Duration.TotalMinutes(endTime - startTime)
in
if duration > 0 then duration else 0
))
in
WorkingDuration),
#"Добавлен пользовательский объект5" = Table.AddColumn(#"Добавлен пользовательский объект4", "Скорость обработки отклика(МПП)", each CalculateMinutes(
[#"Дата и время взятия отклика в работу(Менеджер)"],
[#"Дата и время окончания работы с откликом(Менеджер)"],
Time.From([Время начала]),
Time.From([#"Время окончания "]),
Table.Column(Holidays, "HolidayDate")
)),
#"Измененный тип4" = Table.TransformColumnTypes(#"Добавлен пользовательский объект5",{{"Скорость взятия отклика(МПП)", type number}, {"Скорость обработки отклика(МПП)", type number}}),
#"Условный столбец добавлен5" = Table.AddColumn(#"Измененный тип4", "Превышение норматива", each if [#"Скорость взятия отклика(МПП)"] > 15 then 1 else 0),
#"Замененные ошибки" = Table.ReplaceErrorValues(#"Условный столбец добавлен5", {{"Превышение норматива", 0}}),
#"Добавлен пользовательский объект6" = Table.AddColumn(#"Замененные ошибки", "Фактор_итог", each if [Фактор] = "Скорость обработки" and [Превышение норматива] = 1 then "Другое" else [Фактор]),
#"Объединенные запросы3" = Table.NestedJoin(#"Добавлен пользовательский объект6", {"Фактор_итог"}, Критерии, {"Фактор"}, "Критерии", JoinKind.LeftOuter),
#"Развернутый элемент Критерии1" = Table.ExpandTableColumn(#"Объединенные запросы3", "Критерии", {"Критерий"}, {"Критерий_итог"})
in
#"Развернутый элемент Критерии1" |
