Рішення LuckyTemplates Between Dates Working Time

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

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

Зміст

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

Набір даних для робочих годин LuckyTemplates між датами

Переходимо до редактора Power Query. 

Ось наші дані для цього завдання. У нас є таблиця зі стовпцями для імені співробітника, дати початку, часу початку, дати завершення, часу завершення та стану.

Фільтрування рядків

Давайте почнемо з вибору рядків на основі їх статусу, і редактор запитів може допомогти нам із цією логікою. Клацніть стрілку спадного меню поруч із пунктом Статус. Виберітьі виберіть Не починається з. 

У вікні «Рядки фільтрів» , яке з’явиться, напишіть «e». Потім натисніть OK .

Поки що нічого не станеться, і це добре, тому що ми пишемо малу літеру «e» і «Виключити» в наших даних, які починаються з великої літери «E».

Щоб виправити це, ми використаємо додатковий третій параметр Text.StartsWith . У рядку формул перейдіть до кінця функції Text.StartsWith. Додайте кому та введіть наш Comparer.OrdinalIgnoreCase. Клацніть галочку, і це має видалити рядки Виключити .

Звичайно, у нас може бути хтось, хто помилково набрав «Виключити» без початкової букви «Е». У такому випадку ми можемо створити додаткову логіку, яка буде відповідати ситуації. 

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

Зверніть увагу, що порівняно з першим пунктом ми замінили «e» на «x». Ми продовжуємо ігнорувати регістр і беремо дужки навколо обох наших речень. Клацніть галочку, і ми повинні отримати таблицю без рядків, що містять статус Виключити або Виключити .

Додавання спеціального стовпця

Тепер перейдемо до основної частини цього завдання. Ми додамо спеціальний стовпець за допомогою інтерфейсу користувача, а потім перейдемо до редактора. 

Почніть із значка міні-таблиці у верхньому лів��му куті таблиці. Натисніть на нього та в спадному списку виберіть Додати . 

На даний момент ми зацікавлені лише в тому, щоб отримати дані, які нам знадобляться пізніше. Оскільки ми працюватимемо з кількома полями, давайте створимо запис за допомогою ініціалізаторів записів, представлених у квадратних дужках. 

Давайте створимо змінні та призначимо доступні стовпці. Почніть із введення SD , який ми прирівняємо до дати початку. Ми можемо зробити це, натиснувши «Дата початку» у стовпці праворуч.

Ми зробимо те саме для змінних ST, ED та ET , які буде призначено для Start Time, Stop Date та Stop Time відповідно. Потім закрийте запис закриваючою дужкою. Натисніть OK.

Спеціальний стовпець із записами буде додано до таблиці. Натисніть білий простір поруч із записом , і відкриється панель попереднього перегляду. Це призведе до всіх значень із цього рядка.

Час роботи 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 знаходиться в останньому рядку. Давайте спробуємо детально розібратися в одному із записів. Клацніть правою кнопкою миші перший результат і виберіть Додати як новий запит . 

Ми отримаємо той самий підсумок, що й раніше, і якщо ми натиснемо таблицю , ми отримаємо значення дати та години .

Але нас в першу чергу цікавить підсумкова вихідна таблиця. Щоб зосередитися на цьому, давайте видалимо запит довідки, клацнувши правою кнопкою миші Custom і вибравши Delete . Підтвердьте, натиснувши Видалити у спливаючому вікні.

Після видалення ми можемо повернутися до нашого початкового запиту та знову клацнути білий простір поруч із записом. Він покаже ті ж результати, що й раніше. Але цього разу давайте розгорнемо рядок формул і викличемо [t] поруч із нашими закриваючими дужками.

У стовпці «Спеціальний» ми бачимо, що кожен запис змінено на таблицю . 

Час роботи LuckyTemplates між датами: проекція

У таблиці, яку ми розглядаємо, є лише два стовпці, які цікавлять: ім’я співробітника та наш спеціальний стовпець. Ми можемо використати проекцію, щоб зберегти лише ці два стовпці з цієї таблиці. 

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

Ми повинні отримати такий результат.

Ми можемо розгорнути, натиснувши піктограми зі стрілками  поруч із Custom . Зніміть прапорець Використовувати оригінальну назву стовпця як префікс і натисніть OK.

Наша таблиця повинна вийти такою.

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

Все, що залишилося зробити, це агрегувати ці значення. Для цього виберіть стовпець Staff Name і перейдіть до Transform. 

У спливаючому вікні виберіть Години у стовпці Значення , оскільки це те, що ми хочемо агрегувати. Потім виберіть Сума в розділі . Потім натисніть OK.

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

Висновок

У цьому блозі ви побачили один підхід до завдання Access Analytic Start Stop Challenge. Ви навчилися розраховувати загальну кількість годин, відпрацьованих кожним співробітником за місяць, і як справлятися з можливими помилками у ваших даних. 

За допомогою цієї методики та LuckyTemplates робочі години між датами можна легко розрахувати у вашій організації або як практикум для поглиблення ваших знань і навичок LuckyTemplates.