Период отчёта в Power Query: параметры из ячеек и проверка границ

Каждый месяц аналитик открывает Power Query и меняет две даты в фильтре. Однажды забывает обновить правую границу, и сводная за октябрь содержит только первую половину месяца. Удобнее вынести период в ячейки книги, но простая ссылка на ячейку ещё не решает проблему. Нужно проверить количество строк параметров, пустые значения и порядок границ, а затем одинаково обработать время внутри последнего дня.

Подготовить небольшую таблицу параметров

На отдельном листе создайте таблицу Excel с именем ReportParams, двумя столбцами Start и End и одной строкой. Для примера Start равен 01.10.2026, End — 31.10.2026. В ячейках храните даты, а не подписи вроде «октябрь». Вторую таблицу назовите SalesData; у неё должны быть ID, OrderAt и Amount. OrderAt содержит дату и время заказа. Имена таблиц задаются в конструкторе таблиц Excel, имена листов для кода несущественны.

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

Включить последний день целиком

Фильтр до 31 октября в полночь исключит почти все заказы последнего дня, если OrderAt содержит время. Вместо условия «меньше или равно End» используем начало следующего дня как исключительную верхнюю границу. Тогда окно включает 1 октября 00:00 и 31 октября 23:59, но исключает 1 ноября 00:00. Полуоткрытые окна также удобно стыковать между месяцами без двойного включения границы.

let
    Workbook = Excel.CurrentWorkbook(),
    RawParams = Workbook{[Name="ReportParams"]}[Content],
    P = if Table.RowCount(RawParams)=1 then RawParams{0}
        else error "ReportParams must contain exactly one row",
    S = try Date.From(P[Start], "ru-RU"),
    E = try Date.From(P[End], "ru-RU"),
    StartDate = if S[HasError] then error "Invalid Start"
        else if S[Value]=null then error "Start is required" else S[Value],
    EndDate = if E[HasError] then error "Invalid End"
        else if E[Value]=null then error "End is required" else E[Value],
    Bounds = if StartDate > EndDate then error "Start must not exceed End"
        else [Lower=DateTime.From(StartDate),
              Upper=DateTime.From(Date.AddDays(EndDate,1))],
    Source = if Bounds[Lower] < Bounds[Upper]
        then Workbook{[Name="SalesData"]}[Content]
        else error "Invalid period",
    Typed = Table.TransformColumnTypes(Source,
        {{"OrderAt", type datetime},{"Amount", type number}}, "ru-RU"),
    Result = Table.SelectRows(Typed,
        each [OrderAt] >= Bounds[Lower] and [OrderAt] < Bounds[Upper])
in
    Result

Проверить границы маленькой выборкой

В пустом запросе откройте расширенный редактор, вставьте код и загрузите результат в отдельную таблицу. Для контрольной выборки добавьте в SalesData заказы на 30 сентября 23:59, 1 октября 00:00, 31 октября 23:59 и 1 ноября 00:00. В результат должны попасть только два средних. Затем поменяйте Start на дату позже End: запрос должен выдать понятную ошибку, а не пустой отчёт с убедительным нулём.

Проверьте также пустую дату и две строки параметров. Ошибка имени столбца Start или End должна исправляться в таблице, а не обходиться значением по умолчанию. Для рабочего процесса рядом с результатом полезно показывать фактически выбранный период и время обновления. Пользователь должен видеть, какие данные содержит сохранённая книга, даже если после последнего обновления кто-то уже поменял ячейки параметров.

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

Пример предполагает, что даты параметров и OrderAt относятся к одному локальному календарю. Если источник хранит UTC, а отчёт строится по московским дням, сначала задайте правило перевода временной зоны и только потом сравнивайте границы. Удаление времени через Date.From у каждой строки может скрыть эту разницу. При работе с серверным источником также проверьте, переносится ли фильтр в источник: корректный результат и эффективная загрузка — отдельные вопросы.

Продолжить работу с данными

Курс «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов» связывает подготовку источников с полным обновлением и сохранением отчёта. После настройки параметров следующий шаг — собрать модель и дашборд, в котором период, срезы и контроль итогов работают согласованно; эти задачи представлены в программе. Откройте программу платного курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».