Профилирование данных Power Query: почему первые 1 000 строк пропускают ошибки

1. Откройте запрос и определите, что проверяете

Инструкция относится к настольному Excel для Windows с редактором Power Query. Откройте «Данные → Запросы и подключения», найдите нужный запрос и выберите «Изменить» в его контекстном меню. Такой путь описан в Microsoft Support. Названия элементов могут различаться в зависимости от языка и версии Excel.

Сохраните исходный файл и копию книги. Запишите кабинет, период и назначение выгрузки: например, подготовка операций за сентябрь к отчёту по товарам. Проверка случайного запроса с похожим названием даст аккуратные показатели о другом наборе. Если будете пробовать фильтры, сделайте Duplicate / «Дублировать» запрос и назовите копию «Проверка выгрузки».

В списке «Применённые шаги» выберите состояние после чтения таблицы и настройки типов, но до исключения проблемных строк. Microsoft поясняет: при выборе шага редактор показывает именно его результат. Если панель закрыта, включите «Вид → Параметры запроса». Запишите имя выбранного шага рядом с результатом проверки.

2. Включите три инструмента профилирования

На вкладке View / «Вид» в группе предварительного просмотра включите Column quality, Column distribution и Column profile — качество, распределение и профиль столбца. Согласно Microsoft Learn, первый инструмент показывает состояния значений, второй — частоты, третий — подробную статистику выбранного поля.

Начните с полей, от которых зависит отчёт: ID операции, SKU, дата и сумма. Для каждого заранее сформулируйте ожидание. SKU может повторяться в продажах, дата должна относиться к нужному периоду, а сумма должна иметь согласованный смысл. Одно универсальное требование «все значения разные» испортит такую проверку.

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

3. Переключите охват на весь набор

В левом нижнем углу редактора нажмите сообщение о профилировании первых 1 000 строк и выберите вариант для всего набора данных. Это переключение описано в справке Microsoft по инструментам профилирования. Дождитесь завершения расчёта и проверьте, что подпись режима действительно изменилась.

Отдельно просмотрите применённые шаги. Если при разработке добавляли Keep First Rows / «Сохранить первые строки», этот шаг ограничивает сам результат запроса. Настройка профиля не отменяет такое ограничение. Microsoft рекомендует убрать временный шаг ограничения перед обработкой полного набора. Рабочие фильтры периода и кабинета сохраняйте только после проверки их условий.

В журнале удобно писать: «Запрос — Продажи сентября; шаг — Проверены типы; охват — весь набор; файл — сохранённая версия выгрузки». Так другой человек сможет повторить именно вашу проверку. Запись «в Excel ошибок нет» не объясняет, какие данные вы фактически просмотрели.

4. Учебный пример: ошибка в последних 500 строках

Представим сохранённую таблицу из 1 500 операций. В этом учебном примере порядок строк известен и не меняется: первые 1 000 содержат корректные числовые суммы, среди остальных 500 одна запись после преобразования суммы даёт Error. Пропусков нет. Это специально сконструированный набор, а не результат проверки реального магазина.

При ограниченном охвате эта единственная ошибка не участвует в статистике. После полного профилирования на том же шаге она должна войти в расчёт. Её доля равна 1 / 1 500 × 100 ≈ 0,067%. Небольшой процент не делает запись несущественной: без исходной суммы неизвестно, насколько она влияет на итог.

5. Различайте distinct и unique

В распределении столбца distinct означает количество разных значений, а unique — количество значений, появляющихся ровно один раз. Это определения Microsoft. Не переводите оба показателя одинаково как «уникальные» при передаче результата коллеге.

Возьмём отдельный учебный столбец SKU: A, A, B, C, C, D. Здесь шесть строк, четыре разных значения и два значения с единственным появлением — B и D. Для списка продаж это нормальная ситуация: A и C могли продаваться несколько раз. Для справочника с правилом «одна строка на SKU» те же повторы потребовали бы разбора.

Теперь представьте два кабинета, в каждом есть товар A. Профиль одного столбца не знает, что ключ справочника должен состоять из кабинета и SKU. Нельзя по низкому unique автоматически удалять продажи или объединять товары. Сначала определите единицу записи, затем проверяйте повторение нужной комбинации полей.

6. Используйте частоты как повод для проверки

Допустим, в выгрузке обычно встречается несколько складов, а теперь почти все записи относятся к одному. Это повод проверить условия экспорта и фильтра периода. Возможны и реальные изменения продаж. Само распределение не выбирает объяснение: сопоставьте наблюдение с источником и ожидаемым составом данных.

Для проверки повторов в диагностической копии можно выделить поля предполагаемого ключа и применить Home → Keep Rows → Keep Duplicates. Microsoft уточняет, что отбор зависит от выбранных столбцов и учитывает регистр текста. Поэтому A и a нельзя молча признать одним идентификатором: сначала проверьте правило источника.

Сохраняйте исходные коды. Если бизнес-правило действительно допускает единый регистр, нормализуйте отдельное рабочее поле и сравните распределения до и после. Резкое сокращение числа разных значений объясните конкретными парами кодов. Красивый профиль после преобразования сам по себе не доказывает, что товары сопоставлены правильно.

7. Зафиксируйте результат перед отчётом

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

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

Когда данные приняты, переходите к выбору отклонений для разбора в разделе HelpStat «Рука на пульсе». Профилирование в этой инструкции выполняется в Power Query; автоматическая загрузка или проверка вашей таблицы в HelpStat здесь не предполагается.

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

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

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

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

• Microsoft Learn: инструменты профилирования, охват и distinct/unique
• Microsoft Support: управление запросами в Excel
• Microsoft Learn: выбор и описание применённых шагов
• Microsoft Learn: рекомендации по работе с Power Query
• Microsoft Learn: повторяющиеся значения и выбранные столбцы