Power Query позволяет делать множество операций с датами. В частности, мы можем создавать списки дат между фиксированными датами или список дат от начальной даты по сегодняшний/вчерашний день. Попробуем это сделать!
Список с фиксированной датой
Пример: создадим список дат за весь 2023 год.
Сначала создадим пустой запрос. Для этого выбираем в Excel вкладку Данные – Получить данные – Из других источников – Пустой запрос. Также можно создать пустой запрос из редактора Power Query с помощью команд Создать источник – Другие источники – Пустой запрос:
Или так:
Чтобы приступить к созданию списка дат, на вкладке "Просмотр" щелкаете по иконке "Расширенный редактор".
Удаляем данные - Источник = "", которые записаны по умолчанию:
Чтобы получить список дат, нам нужно задать начальную и конечную дату. Создадим переменную start_date. В ней зададим дату начала, с которой будет начинаться наш список. В этом нам поможет оператор #date. Внутри оператора пропишем год (2023), месяц (1)и день (1):
Аналогично, создадим вторую переменную end_date для даты окончания нашего списка. Внутри оператора #date пропишем год (2023), месяц (12) и день (31) - #date(2023,12,31).
Теперь можно приступать непосредственно к созданию списка дат. Создадим следующую переменную dates_list.
Для генерации списка дат будем использовать функцию List.Dates, которая состоит из трёх аргументов: начальная дата, количество элементов и шаг между элементами.
Разберем подробно записанную нами функцию:
List.Dates( start_date, Duration.Days(end_date - start_date) + 1,
#duration(1, 0, 0, 0))
start_date – первая дата в списке, это созданная нами первая переменная
Duration.Days (end_date - start_date)+1) – количество дней в списке. У нас есть две даты – конечная end_date и начальная start_date, вычитанием получим количество дней. Если мы просто вычислим разницу между двумя датами, то получим duration – это специальный тип для временной длительности в Power Query. Для функции List.Dates такой тип не подходит, т.к. в качестве второго аргумента требуется указать число, а не длительность. Обойти этот нюанс нам поможет функция, способная преобразовать длительность в число, Duration.Days, аргументом которой и является duration. И конечно же надо не забыть прибавить 1, т.к вычитая из даты конечной дату начальную мы теряем дату старта.
#duration(1, 0, 0, 0) – шаг между датами. Задаем шаг с помощью оператора #duration, где указываем 1 день, 0 часов, 0 минут, 0 секунд.
Анонсы всех видео, статей и полезностей - в нашем Telegram🔥
Присоединяйтесь, обсуждайте и автоматизируйте!
Теперь результат переменной dates_list оставляем в качестве результата работы всего алгоритма. Для этого в алгоритме после in дописываем dates_list. Для подтверждения написанного кода нажимаем "Готово":
Как результат получаем список дат за весь 2023 год:
Теперь список нужно превратить в таблицу, чтобы мы имели все атрибуты и возможности таблицы. Для этого выбираем в специальной закладке по работе со списками вкладку Преобразование – Преобразовать – в таблицу.
Осталось только переименовать столбец и поменять тип данных. Задаем столбцу новое имя «Список дат», щелкаем по пиктограмме с типом данных и выбираем тип данных – Дата:
Загружаем список дат в Excel выбрав команду «Закрыть и загрузить» на вкладке «Главная».
Динамический столбец дат в Power Query
Бывают ситуации, когда нужно получить список дат по сегодняшний или вчерашний день. Power Query поможет решить и эту задачу.
Пример: создадим список из дат с 01.06.2022 по текущую дату.
Создадим пустой запрос, как показано в примере выше. В расширенном редакторе создадим переменную dates_list и сразу запишем функцию List.Dates. Зададим конечную и начальную даты непосредственно в самой функции без создания промежуточных переменных.
Разберем все составляющие формулы подробнее:
dates_list = List.Dates( #date(2022, 6, 1), Number.From(DateTime.LocalNow() - #datetime(2022, 6, 1, 0, 0, 0)), #duration(1, 0, 0, 0))
#date(2022, 6, 1) — начало списка 2022.06.01, год 2022, месяцев 6, день 1.
Number.From(DateTime.LocalNow() - #datetime(2022, 6, 1, 0, 0, 0)) — это количество элементов в списке. Во втором аргументе мы из сегодняшнего дня вычитаем 1 июня 2022, чтобы получить нужное количество дней. Number.From помогает преобразовать в число длительность дней между сегодняшней датой и 1 июня 2022 года. DateTime.LocalNow() – будет всегда выдавать нам текущую дату. #datetime(2022, 6, 1, 0, 0, 0) будет задавать начальную дату, которую следует вычесть, где год 2022, месяцев 6, дней 1, 0 часов, 0 минут, 0 секунд).
#duration(1, 0, 0, 0) - шаг между датами. Задаем шаг с помощью оператора #duration, где указываем 1 день, 0 часов, 0 минут, 0 секунд.
Результат переменной dates_list оставляем в качестве результата работы всего алгоритма. Для этого в алгоритме после in дописываем dates_list. Для подтверждения нажимаем "Готово".
Преобразуем наш список в таблицу: вкладка Преобразование – Преобразовать – в таблицу.
Переименовываем столбец и задаем тап данных Дата.
Осталось загрузить нашу таблицу с датами в Excel командой «Закрыть и загрузить» на вкладке «Главная».
Теперь каждый день ваш список будет актуальным до текущей даты, главное не забывайте обновлять запрос!
Если бы вам требовалось получить список по вчерашнюю дату, в расширенном редакторе нужно было бы написать вот такую функцию:
Когда вы вычислите количество дней между текущей и начальной датами с помощью конструкции Number.From(DateTime.LocalNow() - #datetime(2022, 6, 1, 0, 0, 0)) , вам нужно из этого количества вычесть один день, чтобы получить "вчера".
Заключение
В этой статье мы разобрали, как создать список между двумя заданными датами. Такой список уникальных дат может понадобиться при работе с Power Pivot в Excel или с календарём в Power BI.
Также, мы рассмотрели варианты создания списков по текущую и вчерашнюю дату. Созданные в Power Query списки дают вам возможность иметь под рукой всегда актуальный и легко обновляемый календарь - как в Excel, так и в Power BI.
Комментарии