Как в Excel очистить и объединить хаотичные данные с помощью Power Query
Хаотичная выгрузка обычно содержит несколько разных проблем сразу: лишние заголовки, смешанные даты, составные поля, дубли и отдельный справочник с расшифровками. Если исправлять каждую новую поставку вручную, трудно понять, какие правила применялись. Power Query позволяет сохранить последовательность преобразований и повторять её при обновлении данных.
Разберём авторский учебный сценарий: у магазина есть две таблицы заказов и справочник клиентов. Требуется собрать продажи за два периода, привести структуру и добавить название клиента по коду. Это самостоятельные задачи: добавление строк и присоединение справочника выполняются разными командами. Пример рассчитан на Excel с редактором Power Query; учебные коды и суммы придуманы.
Сначала определите, что означает одна строка
В нашем реестре строка — одна позиция заказа. Поля: «Заказ», «Позиция», «КлиентКод», «Дата», «Сумма», «ГородКанал». Например, заказ 101 содержит две позиции: тетрадь на 200 и папку на 300. Если удалить дубли только по номеру 101, одна настоящая позиция исчезнет. Поэтому перед очисткой определите единицу данных и ключ, а не начинайте с кнопки удаления повторов.
Для этого примера ключ позиции — пара «Заказ + Позиция». Справочник клиентов имеет другую единицу: одна строка на КлиентКод. Строки продаж и строки справочника нельзя просто складывать друг под другом, даже если обе таблицы выглядят как прямоугольники Excel.
Наш рабочий порядок такой: отдельно привести каждую выгрузку к единой схеме, добавить периоды через Append, присоединить справочник через Merge, проверить число строк и суммы. Это выбранная автором последовательность для конкретного набора данных, а не обязательный порядок любых преобразований.
Откройте запросы и дайте им понятные имена
Импортируйте исходные таблицы в Power Query и назовите запросы Sales_Jan, Sales_Feb и Clients. В Excel список доступен через «Данные → Запросы и подключения», а в редакторе запросы видны слева. Команда редактирования открывает шаги выбранного запроса. Не всякий запрос обязательно выводится на лист: промежуточные преобразования могут использоваться другими запросами.
Запрос описывает подготовку данных, а связанное подключение хранит сведения об источнике и параметрах обновления. Это полезное различие при поиске ошибки: изменение текста шага и изменение пути к файлу решают разные проблемы. Вместо названий «Запрос1» и «Запрос2» используйте имена по роли данных.
Если хотите сохранить исходный запрос и построить следующий этап поверх него, рассмотрите команду Reference — ссылку на результат другого запроса. Duplicate создаёт копию шагов. В учебной работе можно сравнить оба варианта, но заранее решите, должны ли дальнейшие изменения исходной подготовки влиять на производный запрос.
Приведите заголовки и типы к смыслу данных
Посмотрите на первый шаг после источника. Если вместо названий столбцов остались Column1, Column2, а первая строка действительно содержит заголовки, используйте повышение первой строки до заголовков. Если первая строка — название отчёта, сначала отделите её от табличных данных. Не повышайте заголовки повторно, когда они уже определены корректно.
Переименуйте поля января и февраля одинаково: например, «Код клиента» и «КлиентКод» должны получить единое согласованное имя. Затем проверьте Changed Type — шаг изменения типа. Код 0015 лучше сохранить текстом, если начальные нули значимы. Дату и сумму, напротив, не стоит оставлять произвольным текстом ради отсутствия красных ошибок.
При текстовой дате 04/05/2026 уточните исходный формат. Используйте изменение типа с языковым стандартом, соответствующим данным, а затем сверьте день и месяц. Числа с разными десятичными разделителями тоже требуют правильной интерпретации. Замена всех запятых и точек на пустоту может дать технически допустимое, но неверное число.
Разделить поле и склеить подпись — разные операции
В учебном поле «ГородКанал» лежит значение Москва|Розница. Выберите столбец, команду разделения по разделителю и символ |. Для этого формата разделяем на столбцы, получая «Город» и «Канал». Посмотрите, что произойдёт с записью Санкт-Петербург|Интернет: дефис внутри города не должен стать дополнительной точкой разделения.
Если в исходных значениях бывает несколько символов |, сначала решите, разделять по первому вхождению или каждому. В настройках разделения важно и направление результата: строки вместо столбцов могут увеличить количество записей. Выбирайте его по смыслу данных, а не по тому, какой вариант остался выбран после прошлого запроса.
Объединение столбцов пригодится для подписи вроде «Москва — Розница». Выделите нужные текстовые поля в нужном порядке и выберите объединение с разделителем. Если исходные «Город» и «Канал» ещё нужны для фильтрации, сохраните их копии или используйте вариант с новым столбцом. Это объединение значений внутри строки, а не соединение двух таблиц командой Merge Queries.
Append: добавить февральские продажи под январскими
В группе объединения на главной вкладке редактора выберите Append Queries as New, укажите Sales_Jan и Sales_Feb. Получится новый запрос с обеими выгрузками, а исходные запросы останутся отдельными. Операция сопоставляет колонки по именам, а не по их позиции.
Предположим, в январе столбец называется «Сумма», а в феврале случайно «Сумма заказа». Append не догадается, что это одно поле: в результате появятся обе колонки, а отсутствующие значения будут заполнены null. Исправьте согласование схемы до добавления строк, если по смыслу колонка одна.
Для контроля зададим январю две позиции на общую сумму 500, февралю — одну на 700. После Append ожидаются три позиции и сумма 1200. Различайте null, ноль и пустую строку: отсутствие значения не становится подтверждённой нулевой продажей только потому, что так удобнее складывать.
Merge: добавить имя клиента по коду
В запросе объединённых продаж выберите Merge Queries. Продажи будут левой таблицей, Clients — правой. Выделите КлиентКод в обеих таблицах и проверьте одинаковый тип ключей. Для нашего сценария выберите Left Outer: сохраняем все продажи, добавляя совпавшие сведения справочника.
После соединения появится столбец с вложенными таблицами. Разверните из него только необходимое поле «КлиентНазвание». Если совпадения нет, не придумывайте название автоматически: выделите такие коды и проверьте справочник. Возможно, это новый клиент или различие в написании идентификатора.
Авторский контроль: если до соединения было три позиции, а после разворачивания стало четыре, проверьте уникальность КлиентКод справа. Например, код 0015 дважды встречается в справочнике с разными названиями; продажа с этим кодом получает несколько совпадений. Выбор «первой попавшейся» строки не решает противоречие в справочнике.
Дубли удаляйте по установленному правилу
Для поиска повторов используйте Keep Duplicates по нужному ключу. Сначала рассмотрите найденные записи: одинаковая пара номера и позиции может означать повторную выгрузку, исправление суммы или реальную ошибку ввода. Только после этого определите действие.
Power Query учитывает регистр текста при сравнении. Если ABC и abc по правилам вашей системы один код, сначала согласованно нормализуйте регистр. Remove Duplicates не гарантирует сохранение именно первой нужной записи. Если требуется брать последнюю редакцию по времени, нужна отдельная явная логика выбора версии, а не надежда на видимый порядок строк.
Обновление и разбор сбоя
В Excel откройте «Данные → Запросы и подключения». Найдите подключение результирующего запроса и откройте «Свойства» через его контекстное меню; в зависимости от представления панели переключитесь на вкладку подключений. Другой путь — выбрать ячейку загруженной таблицы и открыть свойства подключения из команд обновления на вкладке «Данные».
В параметрах использования проверьте три решения: должно ли подключение участвовать в «Обновить всё», нужно ли обновление при открытии книги и требуется ли обновление через заданный интервал, если этот параметр доступен для подключения. Фоновое обновление поддерживается не для всех видов источников и загрузок; например, документация отдельно ограничивает его для запросов, получающих данные для модели данных.
Для учебного отчёта включите участие в «Обновить всё», затем по необходимости обновление при открытии. Измените одну известную сумму в источнике, выполните обновление и проверьте итог на листе. Если настройка интервала не нужна процессу, не добавляйте её лишь ради наличия расписания. Запишите, какой именно способ обновления выбрали, и проверяйте статус завершения, прежде чем использовать числа.
Загрузите итог в отдельную таблицу и выполните обновление в Excel. Проверьте контрольные три позиции и сумму 1200, а затем измените учебную поставку и повторите. Предварительный просмотр редактора и результат на листе проверяйте отдельно: обновлённое превью само по себе не означает обновление загруженной таблицы.
При сбое последовательно выбирайте элементы списка «Применённые шаги» от начала до первого неверного результата. Если исчез столбец после переименования, ищите шаги, обращающиеся к старому имени. Если новый столбец источника не дошёл до отчёта, проверьте явный выбор и разворачивание полей. Если изменилась сумма, сравните данные до и после Append, Merge и удаления повторов.
Сохраните рядом с книгой короткое описание схемы и контрольных чисел. Цель повторяемой очистки — получить объяснимый результат: какие записи включены, какие исключены и почему. Когда эти правила записаны в шагах, очередная выгрузка требует проверки изменений, а не повторения всей ручной работы с нуля.
