Ошибки и пропуски в Power Query: как сохранить проблемные строки

1. Разделите четыре похожих состояния

В спецификации языка M Microsoft определяет null как отсутствие значения либо неизвестное состояние. Пустая строка — текст без символов, который записывается как "". Строка из одного пробела тоже является текстом, хотя визуально выглядит пустой. Error возникает, когда вычисление не удалось: например, слово «не получено» нельзя превратить в сумму продаж.

Ноль — самостоятельное числовое значение. Он может быть корректным для конкретной операции, если это подтверждает источник. Из null нельзя заключить, что продаж не было: сумма могла ещё не поступить, поле могло быть неприменимо или загрузка оказалась неполной. Смысл пропуска устанавливают по определению поля и исходной записи.

Описанный ниже порядок относится к обработке одного столбца суммы в редакторе Power Query. Исходный столбец уже содержит текст и null, без ошибок чтения источника. Это учебная структура, а не утверждение о формате конкретной выгрузки WB или Ozon.

2. Сохраните исходник до очистки

Оставьте неизменный файл и базовый запрос до числового преобразования. Для обработки создайте копию запроса. Сохраните ID исходной записи, название файла, кабинет и период. Если отдельного ID нет, контрольный номер строки должен относиться к конкретной сохранённой версии файла; при следующей выгрузке тот же номер может обозначать другую операцию.

Назовите поле «Сумма_исходная», а его копию — «Сумма_число». Проверьте применённые шаги: исходный текст должен быть сохранён раньше шага, который мог превратить его в Error. Запишите число строк. В этом сценарии одна строка означает одну исходную операцию; справочные итоги уже отделены от операций.

Заведите журнал исключений с полями «ID записи», «исходное значение», «причина», «решение» и «основание». Записи должны оставаться связанными с источником даже после исправления. Журнал нужен для ответа на вопрос, какие суммы ещё неизвестны, а не для удаления неудобных строк из отчёта.

3. Отметьте пропуски до преобразования в число

На исходном текстовом столбце добавьте пользовательский столбец «Состояние». Для оговорённого набора из текста и null используйте выражение: if [Сумма_исходная] = null then "null" else if [Сумма_исходная] = "" then "Пустая строка" else if Text.Trim([Сумма_исходная]) = "" then "Пробельный текст" else "К преобразованию".

Проверки идут именно в таком порядке: сначала отсутствие значения, затем текст нулевой длины, затем пробельный текст. Microsoft описывает Text.Trim как удаление пробельных символов по краям текста. Здесь функция используется только для проверки; исходное содержимое остаётся рядом. Невидимые символы, которые не относятся к удаляемым пробельным, потребуют отдельного разбора.

Сохраните записи первых трёх состояний в журнале и посчитайте каждую группу. В рабочей копии для числового преобразования оставьте только «К преобразованию». Это осознанно частичный набор; базовый запрос по-прежнему содержит все строки. Значения с уже существующими Error нужно сначала разбирать как ошибки, а не подавать в это выражение.

4. Разберите ошибки, сохранив их причину

Преобразуйте только «Сумма_число» в числовой тип, соответствующий полю. Если число записано текстом с разделителями, заранее установите локаль источника. Microsoft Learn в Dealing with errors различает сбой всего шага и ошибку отдельной ячейки. Неправильный путь к источнику нельзя исправить заменой значений в столбце.

В отдельной копии запроса выделите «Сумма_число» и выберите Home → Keep Rows → Keep Errors / «Главная → Сохранить строки → Сохранить ошибки». Сохраните отобранные ID и исходные значения. При выборе ячейки Error посмотрите причину, сообщение и подробности. Null и пустой текст проверяются отдельно: эта команда ищет ошибки вычисления.

Для сохранения подробностей в столбцах Microsoft показывает другой приём: «Добавление столбца → Пользовательский столбец» с выражением try [Сумма_число]. Раскройте полученную запись в HasError, Value и Error, затем раскройте Error для причины и сообщения. У ошибочной записи Value после раскрытия будет null, поэтому флаг HasError нужно сохранить: иначе ошибка снова станет похожа на исходный пропуск.

5. Проверьте семь учебных записей

Предположим, на шаге до обработки имеются семь записей A–G из таблицы. Состояния null, пустой строки и одиночного пробела заданы уже в Power Query; способ, которым конкретный CSV или Excel передал пустую ячейку, в примере не предполагается. Суммы обозначают одну согласованную величину в рублях. Все числа придуманы для проверки.

После классификации D, E и F попадают в журнал пропусков. Четыре оставшиеся записи проходят числовое преобразование: A, B и C дают числа, G — ошибку. Keep Errors по числовому столбцу должен найти G. Пригодная часть состоит из трёх записей, включая C с нулём, и даёт 1 200 + 800 + 0 = 2 000 ₽.

6. Заменяйте значение только по подтверждённому правилу

Если удалить G, а затем убрать все пропуски, останутся три аккуратные строки на 2 000 ₽. Если заменить все четыре проблемные суммы нулями, получится семь строк с тем же итогом. Оба результата выглядят правдоподобно, но во втором случае появились четыре неподтверждённых числовых значения. Совпадение суммы не выявляет эту подмену.

Разрешение проблемы должно опираться на исходник: исправить формат при известном числе, получить отсутствующее значение или подтвердить, что поле неприменимо. Команда Replace Errors подставляет выбранное значение, но не устанавливает причину ошибки. Удаление ошибки в запросе также не исправляет внешний источник — это отдельно подчёркивает Microsoft Support.

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

7. Сверьте результат и повторите контроль при обновлении

Для учебного набора проверьте равенство: 7 исходных ID = 3 пригодных ID + 4 ID с исключениями. Группы не пересекаются, каждая исходная запись объяснена ровно один раз. Если проверяются несколько столбцов, одна строка может иметь несколько дефектов: считайте уникальные ID проблемных записей, а не складывайте число ошибок всех полей.

Во вкладке View / «Вид» включите качество и профиль столбцов. Microsoft указывает, что по умолчанию профилирование охватывает первые 1 000 строк; переключите его на весь набор данных в нижней части редактора. Это помогает найти проблемы дальше первого экрана, но не подтверждает правильный бизнес-смысл чисел.

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

Когда исходные показатели проверены, переходите к сравнению периодов и товаров во «Внутренней аналитике WB и Ozon» HelpStat. Классификация и исправление данных в этой инструкции выполняются в Power Query; дальнейшие решения должны учитывать оставшиеся исключения.

Источники и методика

Источники ниже помогают проверить определения и возможности отчётов. Методика разбора и учебные расчёты подготовлены редакцией HelpStat. Доступность отчётов и условия работы проверяйте в своём кабинете. Расчётные примеры в статье — учебные, а не результаты клиентов.

Как устроены материалы HelpStat

• Microsoft Learn: значения null и text в языке M
• Microsoft Learn: Text.Trim и пробельные символы
• Microsoft Learn: ошибки шага и ячейки, Keep Errors и Replace Errors
• Microsoft Learn: try, HasError и сохранение подробностей ошибки
• Microsoft Support: удаление ошибок и неизменность внешнего источника
• Microsoft Learn: профилирование первых 1 000 строк и всего набора