Решение LuckyTemplates между датами рабочего времени

В сегодняшнем блоге я продемонстрирую подход к решению Access Analytic Start Stop Challenge , при котором с помощью LuckyTemplates будет рассчитываться общее количество рабочих часов между датами. Вы можете посмотреть полное видео этого урока внизу этого блога .

Задача состоит в том, чтобы рассчитать общее количество часов для сотрудника на каждый месяц и число. Я представил даты начала и окончания, которые могут пересекать несколько месяцев, и принял во внимание статус с учетом возможных опечаток. 

Оглавление

• Набор данных для рабочих часов LuckyTemplates между датами
• Фильтрация строк
• Добавление пользовательского столбца
• Время работы LuckyTemplates между датами: переменные
• Время работы LuckyTemplates между датами: прогноз
• Время работы LuckyTemplates между датами: совокупные значения
• Заключение

Набор данных для рабочих часов LuckyTemplates между датами

Перейдем к редактору Power Query. 

Вот наши данные для этой задачи. У нас есть таблица со столбцами для имени персонала, даты начала, времени начала, даты окончания, времени окончания и статуса.

Фильтрация строк

Давайте начнем с выбора строк на основе их статуса, и редактор запросов может помочь нам с этой логикой. Щелкните стрелку раскрывающегося списка рядом со статусом. Выберите «Текстовые фильтры» и выберите «Не начинается с». 

В появившемся окне Filter Rows напишите «e». Затем нажмите ОК .

Пока ничего не произойдет, и это нормально, потому что мы пишем строчную «е» и «Исключить» в наших данных, которые начинаются с прописной «Е».

Чтобы исправить это, мы будем использовать необязательный третий параметр Text.StartsWith . В строке формул перейдите в конец функции Text.StartsWith. Добавьте запятую и введите наш Comparer.OrdinalIgnoreCase. Нажмите на галочку, и это должно удалить строки «Исключить» .

Конечно, у нас мог быть кто-то, кто ошибочно набрал «Исключить» без начальной буквы «Е». В этом случае мы можем создать дополнительную логику, соответствующую ситуации. 

Мы можем проверить, не содержит ли слово «X», расширив нашу функцию и используя Text.Contains . Для этого добавьте или Text.Contains([Status], «x», Comparer.OrdinalIgnoreCase .

Обратите внимание, что по сравнению с первым предложением мы заменили «e» на «x». Мы продолжаем игнорировать регистр и заключаем в скобки оба наших предложения. Нажмите на галочку, и мы должны получить таблицу без строк, содержащих статус Exclude или Exclude .

Добавление пользовательского столбца

Давайте теперь перейдем к основной части этой задачи. Мы добавим пользовательский столбец с помощью пользовательского интерфейса, а затем переключимся в редактор. 

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

На данный момент нас интересуют только те данные, которые нам понадобятся позже. И поскольку мы будем работать с несколькими полями, давайте создадим запись, используя инициализаторы записи, представленные в квадратных скобках. 

Давайте создадим переменные и назначим доступные столбцы. Начните с ввода SD , которое мы приравняем к дате начала. Мы можем сделать это, нажав Дата начала в столбце справа.

Мы сделаем то же самое для переменных ST, ED и ET , которые будут назначены для Start Time, Stop Date и Stop Time соответственно. Затем закройте запись закрывающей скобкой. Нажмите «ОК».

Пользовательский столбец с записями будет добавлен в таблицу. Нажмите на пустое место рядом с записью , и откроется панель предварительного просмотра. Это принесет все значения из этой строки.

Время работы LuckyTemplates между датами: переменные

Затем мы можем отформатировать наши переменные через окно расширенного редактора . Вот как должно выглядеть окно расширенного редактора. 

Затем давайте поместим наши переменные в отдельные новые строки.

Теперь мы можем расширить необходимую нам логику. Таким образом, вместо записи мы могли бы также использовать здесь вложенное выражение let. Обратите внимание, что любой из них будет работать, потому что у нас есть несколько имен переменных или полей с присвоенными им значениями.

Наша дата начала в настоящее время записывается как текстовое значение, и нам нужно преобразовать его в правильный формат даты. Для этого оберните его  функцией Date.FromText .

Для времени начала , которое также отформатировано как строка, нам нужно преобразовать его в число. Затем мы можем разделить его на сто и округлить, чтобы удалить десятичные знаки. 

Используя функцию Number.From , разделите наше значение Start Date на 100 . Затем поместите их в следующую функцию Number.Round и добавьте , 0 в конце, чтобы округлить без десятичных знаков. 

Для даты окончания и времени окончания мы просто скопируем те же функции, что и для даты начала и времени начала соответственно, и заменим их соответствующими переменными.

Как только переменные будут правильно отформатированы, создайте еще одно поле в нашей записи для дополнительной логики. Создайте новую строку, нажав Enter. 

Нам нужно построить список дат от первой даты до конечной даты. Мы назовем эти даты LD и воспользуемся функцией List.Dates . 

Первым параметром этой функции является дата начала , которая будет нашей SD. Затем ему требуется подсчет в виде числа или разницы между датой начала и датой окончания. Мы можем получить это, используя функцию Number.From , за которой следует ( ED – SD) + 1 . Обратите внимание, что «+1» не отображается на следующих наборах скриншотов, но +1 должен быть .

Третий параметр называется шагом в качестве продолжительности , и мы хотим увеличить его на один день. Мы можем получить это, используя Duration.From (1). 

Мы создали список с датами от самой первой даты начала до даты окончания. Далее нам нужно создать список времен, соответствующих этому. 

Давайте назовем это LT для времени списка. LT может иметь дневной период, равный одному дню, что требует несколько иной логики, чем если бы он охватывал несколько дней. Так что это то, для чего мы должны создать условие. 

Мы хотим, чтобы условие говорило: «Если наша дата начала совпадает с датой окончания, то время окончания минус время начала». Результаты должны быть в формате списка, поэтому мы используем инициализатор списка, представленный { } . 

Теперь, если у нас есть диапазон дат, который охватывает несколько дней, то первая дата будет равна 24 минус время начала. Мы добавим это к нашему условию как оператор else . Опять же, отформатируйте это как список, используя фигурные скобки.

На данный момент мы не учитывали другие целые дни, поэтому мы будем использовать символ амперсанда (&) для добавления нескольких списков. Для каждого целого дня создайте список со значением 24, представляющим 24 часа, которые у нас есть в сутках. Для этого  мы будем использовать List.Repeat .

С помощью функции List.Repeat создайте список, содержащий 24, и повторите это количество раз, подсчитав количество дней в LD . Для этого используйте List.Count (LD)   , затем вычтите 2, потому что у нас есть отдельный список для даты начала, и мы создадим другой список для времени окончания. 

По сути, это создаст список только с 24 часами для каждого целого дня. 

В конце времени мы можем снова добавить его в список, используя амперсанд. Затем, используя инициализаторы списка, давайте вызовем ET. 

На данный момент у нас есть два больших списка — список дат и список времени — с одинаковой длиной. Из этих двух списков мы можем построить одну таблицу. 

В новой строке создайте еще одну переменную для таблицы, которую мы назовем t , и используйте функцию Table.FromColumns . Для этой функции требуются списки как список , и мы будем использовать наш LD. 

Наш LD содержит отдельные даты от даты начала до даты окончания. Мы можем преобразовать этот список дат в значение даты конца месяца, передав LD и вызвав функцию Date.EndOfMonth . Затем в следующей строке давайте также  получим наш LT .

Нажмите Enter , чтобы создать еще одну строку и установить для нашей таблицы тип table . Наша таблица будет иметь два столбца, первый столбец будет столбцом даты. Итак, создайте столбец Date и вызовите функцию Date.Type .

Второй столбец будет нашим столбцом «Часы» , и это будет целое число. Таким образом, мы создадим столбец Hrs и вызовем Int8.Type.

У нас есть закрывающая скобка, чтобы закрыть нашу запись, и закрывающая скобка, чтобы закрыть нашу функцию Table.AddColumn . Нажмите «Готово» , и мы получим список записей в пользовательском столбце. 

Снова нажмите на пустое место рядом с каждой записью , и таблица должна открыться в нижней части экрана. 

Обратите внимание, что наша таблица t находится в последней строке. Давайте попробуем углубиться в один из рекордов. Щелкните правой кнопкой мыши первый результат и выберите «Добавить как новый запрос» . 

Мы получим ту же сводку, что и раньше, и если мы нажмем на Table , мы должны получить наши значения Date и Hrs .

Но нас в первую очередь интересует конечная выходная таблица. Чтобы сосредоточиться на этом, давайте удалим запрос справки, щелкнув правой кнопкой мыши Custom и выбрав Delete . Подтвердите, нажав Удалить во всплывающем окне.

После удаления мы можем вернуться к нашему первоначальному запросу и снова щелкнуть пробел рядом с записью. Он покажет те же результаты, что и раньше. Но на этот раз давайте развернем строку формул и назовем [t] рядом с закрывающими скобками.

В столбце Custom мы видим, что каждая запись изменена на Table . 

Время работы LuckyTemplates между датами: прогноз

В таблице, которую мы рассматриваем, нас интересуют только два столбца — имя сотрудника и наш пользовательский столбец. Мы можем использовать проекцию, чтобы сохранить только эти два столбца из этой таблицы. 

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

Мы должны получить этот результат.

Мы можем развернуть вложенную таблицу , щелкнув значки со стрелками  рядом с Custom . Снимите флажок «Использовать исходное имя столбца в качестве префикса» и нажмите «ОК».

Наша таблица должна выглядеть так.

Время работы LuckyTemplates между датами: совокупные значения

Все, что осталось сделать сейчас, это агрегировать эти значения. Для этого выберите столбец «Имя персонала» и перейдите к «Преобразование». 

Во всплывающем окне выберите «Часы» в столбце «Значения» , потому что это то, что мы хотим агрегировать. Затем выберите «Сумма» в разделе «Функция совокупного значения» . Затем нажмите ОК.

Наш окончательный результат будет выглядеть так. 

Создание таблицы дат в LuckyTemplates Самый быстрый способ
Сравнение времени для нестандартных таблиц дат в LuckyTemplates
DateTime Value: как убрать секунды

Заключение

В этом блоге вы видели один из подходов к решению задачи Start Stop от Access Analytic. Вы узнали, как рассчитать общее количество часов, отработанных каждым сотрудником в месяц, и как бороться с возможными опечатками в ваших данных. 

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