Вечный календарь праздников – POTW #12 (решение Power Query)

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

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

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

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

Оглавление

• Отправная точка для создания вечного календаря
• Извлечение лет из таблицы дат
• Получение числа из столбца дня
• Вызов пользовательской функции для вечного календаря праздников
• Создание списка дат
• Применение условной логики
• Обзор М-кода для вечного календаря праздников
• Заключение

Отправная точка для создания вечного календаря

Этот сырой праздничный стол — наша отправная точка. Чтобы собрать воедино дату, нам нужны год, месяц и день для конкретных дат. Не хватает только года.

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

Извлечение лет из таблицы дат

Чтобы сделать это решение динамичным, я собираюсь получить годы из таблицы дат . В строке формул вы можете видеть, что я использовал проекцию, чтобы получить таблицу с одним столбцом из моей таблицы дат. Это, конечно, будет содержать дубликаты. Чтобы удалить их, я добавил 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 , чтобы сохранить только те даты, которые относятся к определенному дню недели.

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

Применение условной логики

Наконец, мы применим условную логику для построения или извлечения даты. Если день недели равен null , что указывает на то, что у нас есть конкретная дата, мы можем использовать встроенный #date для создания значения даты.

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

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

Во всех остальных случаях мы используем List.Skip .

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

Соберем все воедино и перейдем к запросу решения. Для исходного шага мы вызываем таблицу дат праздников RAW. Далее мы добавим столбец, который возвращает список с датами.

Обзор М-кода для вечного календаря праздников

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

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

Когда мы щелкаем сбоку в пустом месте, мы можем увидеть предварительный просмотр этого списка внизу.

Следующий шаг — расширить этот список и добавить CelebratedOnDate .

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

Наконец, последний шаг — изменить типы.

Заключение

Напомню, что — это постоянная серия, которая выходит каждую 1-ю и 3-ю среду на форуме LuckyTemplates. Проблема, опубликованная на 1-й неделе, требует решения на основе DAX, а проблема на 3-й неделе требует решения на основе Power Query.

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

Мелисса