Вічний календар свят – POTW #12 (Power Query Solution)

У цій публікації блогу ми розглянемо моє рішення для . Для цього завдання ми переглянули перше завдання LuckyTemplates «Проблема тижня», де ми створили вічний календар свят із поєднання конкретних і відносних дат. Але цього разу ми будемо використовувати лише Power Query . Ви можете переглянути повне відео цього підручника внизу цього блогу.

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

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

Коли я сам працював над цим сценарієм, я зрештою створив кілька рішень. Той, який я використав, найкраще ілюструє, як можна не лише розбити проблему на менші частини , але й зробити їх видимими . Це дуже корисно, якщо ви зіткнулися з помилкою або неочікуваними результатами.

Зміст

• Відправна точка для створення вічного календаря
• Отримання років із таблиці дат
• Отримання числівника зі стовпця «День».
• Виклик спеціальної функції для постійного календаря свят
• Створення списку дат
• Застосування умовної логіки
• Перегляд коду M для вічного календаря свят
• Висновок

Відправна точка для створення вічного календаря

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

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

Отримання років із таблиці дат

Щоб зробити це рішення динамічним, я збираюся отримати роки з таблиці дат . У рядку формул ви можете побачити, що я використав проекцію, щоб отримати таблицю з одним стовпцем із моєї таблиці дат. Це, звичайно, міститиме дублікати. Щоб видалити їх, я додав Table.Distinct .

Коли я клацну збоку в білому просторі біля будь-якої з цих таблиць, ви також побачите список із різними роками, присутніми в моєму запиті таблиці дат.

Нам дали назву місяця , але насправді нам знадобиться номер місяця . Тепер, коли ми об’єднуємо рік із назвою місяця, ми можемо використовувати функцію Date.From , щоб повернути перше число цього місяця як значення дати.

Додавши Date.Month , ми залишимо лише номер місяця для цієї конкретної дати. Для січня повертається 1. Для лютого повертається 2 і так далі.

Отримання числівника зі стовпця «День».

Щоб отримати цифру зі стовпця дня, ми вирішили не розбивати стовпець і замість цього витягувати значення. Як ви можете бачити в рядку формул, ми використали Text.BeforeDelimiter і використали цей пробіл для ідентифікації правої та лівої частини текстового рядка.

Якщо місця немає, ми хочемо повернути все, що залишилося в стовпці дня. Отже, для першого запису повертається 1. Для другого запису, який містить цей пробіл, повертається перша частина текстового рядка, слово Third .

Ми використаємо подібний підхід, щоб отримати назву дати, але використаємо функцію List.PositionOf . Це повертає зміщення від нуля значення у списку, переданого як перший аргумент, і повертає -1, якщо значення не відображається в цьому списку.

Для першого запису він передає значення 1, якого немає в нашому списку. Ось чому він повертає значення -1. Для другого запису повертається значення Monday .

Він передає це нашій функції та повертає 1, оскільки це другий елемент у нашому списку. Далі ми замінимо всі значення -1 на null .

На даний момент ми створили всі будівельні блоки, необхідні для виклику спеціальної функції.

Виклик спеціальної функції для постійного календаря свят

У рядку формул ми бачимо, що функція приймає чотири аргументи: рік , місяць , число і день тижня .

Але як він визначає це значення дати? Ми знову використаємо List.PositionOf , щоб отримати числове значення для числа. У нас є список, що містить перше, друге, третє та четверте. Коли ми передаємо числове значення List.PositionOf , воно повертає зміщення від нуля.

Знову ж таки, якщо значення відсутнє, воно повертає -1. Отже, для першого запису ми передаємо значення 1. 1 немає в цьому списку, тому він повертає значення -1. Для другого запису ми передаємо значення Third, яке є третім елементом у нашому списку. Це повертає значення 2.

Створення списку дат

Давайте тепер створимо список дат. Ми визначимо дату початку для цього місяця, визначимо кількість днів у цьому місяці та використаємо List.Dates , щоб створити список, який містить усі дні цього місяця.

Далі ми використаємо List.Select , щоб зберегти лише ті дати, які припадають на певний день тижня.

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

Застосування умовної логіки

Нарешті, ми застосуємо умовну логіку для побудови або отримання дати. Якщо день тижня дорівнює нулю , що вказує на те, що ми маємо конкретну дату, ми можемо використати внутрішній #date для створення значення дати.

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

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

Для всіх інших випадків ми використовуємо List.Skip .

Ми можемо отримати значення на основі цієї позиції в списку. Для Дня подяки цифра є четвертою, а четверта повертає 3. Це пропускатиме перші три значення у списку та завжди повертатиме четвертий елемент.

Давайте зведемо все разом і перейдемо до запиту розв’язання. Для вихідного кроку ми викликаємо таблицю святкових дат RAW. Далі ми додамо стовпець, який повертає список із датами.

Перегляд коду M для вічного календаря свят

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

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

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

Наступним кроком буде розгорнути цей список і додати CelebratedOnDate .

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

Нарешті, останнім кроком є ​​зміна типів.

Висновок

Просто коротко про це нагадаюце триваюча серія, яка відбувається кожної 1-ї та 3-ї середи на форумі LuckyTemplates. Проблема, опублікована на 1-му тижні, потребує рішення на основі DAX, тоді як проблема на 3-му тижні вимагає рішення на основі Power Query.

Якщо вам сподобався вміст, описаний у цьому POTW, підпишіться на телеканал LuckyTemplates, щоб отримати більше. У нас постійно надходить величезна кількість вмісту, створюваного мною та низкою творців контенту, які всі прагнуть покращити спосіб використання LuckyTemplates і Power Platform.

Меліса