Power Query try: отдельный журнал ошибок вместо тихой замены на null

После обновления отчёта несколько сумм превратились в ошибки преобразования. Быстрое решение — заменить ошибки на null. Сводная снова строится, но выручка оказывается ниже, а причины потеряны. Через месяц трудно понять, где действительно отсутствовала сумма, а где источник прислал «ошибка» вместо числа. В Power Query можно сохранить исходное значение и описание сбоя в отдельном журнале, одновременно подготовив корректные строки для расчёта.

Сохранить результат попытки преобразования

Выражение try возвращает запись. При успехе в ней HasError=false и Value с результатом. При сбое HasError=true и Error с причиной и сообщением. Поэтому полезнее сначала добавить такую запись отдельным столбцом, а не сразу применять otherwise null. Идентификатор исходной строки и сырой текст должны остаться рядом: только по сообщению «не удалось преобразовать» бухгалтер не найдёт документ.

В примере есть три строки: нормальная сумма «120,50», неправильное слово и null. Число разбираем с культурой ru-RU, чтобы десятичная запятая имела определённый смысл. Настоящий null остаётся отсутствующим значением и не считается ошибкой разбора. Дальше бизнес может решить, разрешены ли пропуски суммы, но это отдельная проверка обязательного поля, а не та же техническая ошибка.

Получить две таблицы из одного основания

Вставьте пример в пустой запрос через расширенный редактор. Результат — запись с таблицами Good и Errors. В редакторе можно перейти по каждой из них. В рабочей книге удобнее назвать запрос основания Parsed и сделать два ссылающихся запроса: один отбирает успешные строки, другой — ошибки. Не копируйте логику разбора в два независимых места: при изменении правила они легко разойдутся.

let
    Source = #table(type table [ID=Int64.Type, Raw=nullable text],
        {{1,"120,50"},{2,"ошибка"},{3,null}}),
    Parsed = Table.AddColumn(Source, "Parse", each try Number.FromText([Raw], "ru-RU")),
    WithFlag = Table.AddColumn(Parsed, "HasError", each [Parse][HasError], type logical),
    GoodRows = Table.SelectRows(WithFlag, each not [HasError]),
    Good = Table.AddColumn(GoodRows, "Amount", each [Parse][Value], type nullable number),
    BadRows = Table.SelectRows(WithFlag, each [HasError]),
    WithMessage = Table.AddColumn(BadRows, "Message", each [Parse][Error][Message], type text),
    Errors = Table.SelectColumns(WithMessage, {"ID","Raw","Message"})
in
    [Good=Table.RemoveColumns(Good, {"Parse"}), Errors=Errors]

Проверить полноту, а не только отсутствие красных ячеек

Ожидаются две успешные строки: ID 1 с Amount 120,5 и ID 3 с null. В журнал попадает ID 2 с исходным словом и диагностикой. Сумма количеств строк Good и Errors равна трём. Это простая сверка, которая ловит тихое удаление проблемных записей. Сумма чисел корректной части равна 120,5, но её нельзя подписывать как полную выручку периода, пока не определена сумма ошибочного документа.

Для обязательных сумм добавьте отдельную проверку null: такая строка может быть технически преобразована успешно и при этом непригодна для бизнеса. Аналогично число минус сто не вызывает ошибку Number.FromText, однако для некоторых документов оно запрещено. Разделите причины журнала на техническое преобразование, пропуск и нарушение правила. Тогда исправление будет адресным: формат, заполнение поля или разбор операции возврата.

Когда нужно остановить весь отчёт

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

Сохраняйте вместе с ошибкой файл, номер документа и дату загрузки, если они есть в источнике. После исправления проверьте, что ID исчез из журнала и появился среди успешных строк с ожидаемой суммой. Такая обратная сверка завершает цикл обработки: проблему можно найти, исправить и доказать, что конкретный документ вернулся в отчёт.

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

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