Как очистить и объединить данные в Excel через Power Query

Два файла продаж могут выглядеть одинаково для человека и при этом плохо объединяться: в одном код клиента — число, в другом текст, названия колонок различаются, а сумма записана с запятой. Если каждый месяц исправлять это вручную, следующий файл снова потребует тех же действий. Power Query позволяет сохранить последовательность преобразований и повторить её при обновлении источников.

Разберём авторский учебный случай в Excel для Windows: две таблицы продаж и справочник менеджеров. Цель — получить три корректные строки с суммой 2 450,50 и присоединить к каждой менеджера. Эти числа — контрольные данные примера, не реальные продажи.

Сначала определить форму итоговой таблицы

Одна строка нашего результата означает один заказ. Планируем колонки OrderID, Date, ClientID, Amount и Manager. Это важное условие: если в исходных файлах строки на самом деле обозначают позиции заказа, удалять повторяющийся OrderID как «дубликат» нельзя.

Источник	OrderID	Date	Код клиента	Amount
Файл А	A1	01.11.2026	001 с пробелом в конце	1 200,50 текстом
Файл А	A2	02.11.2026	002	350,00 текстом
Файл Б	B1	03.11.2026	001	900,00 текстом

В файле А колонка называется ClientID, в файле Б — client_code. В справочнике есть 001 → Ирина и 002 → Павел, по одному соответствию на код. Исходные файлы сохраняем как входные; результат запроса загружаем отдельно.

Подключиться к источникам, а не копировать значения

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

Имена пунктов могут зависеть от версии Excel и локализации, но различие сохраняется: вы создаёте запрос к источнику. В редакторе Power Query справа видна последовательность Applied Steps. При выборе шага можно посмотреть состояние данных на этом этапе. Не исправляйте ошибку в финальной выгрузке, если нужно изменить правило подготовки: при обновлении результат снова будет построен из источника и шагов.

Создайте отдельные запросы SalesA, SalesB и Managers. Для каждого проверьте, что выбран именно диапазон данных, а не титульная строка отчёта или соседний лист. Первая загрузка — удобный момент увидеть ненужные строки «Итого» и повторные заголовки, а не переносить их дальше в расчёт.

Привести структуру и текстовые ключи

В SalesB переименуйте client_code в ClientID. Сверьте остальные названия и смысл полей. Если первая строка содержит названия колонок, примените использование первой строки как заголовков; если заголовки уже определены правильно, не повышайте первую строку заказа ещё раз.

ClientID задайте текстовым типом до преобразования, способного потерять ведущие нули. Код 001 в этом примере — идентификатор, не число для сложения. Если источник уже сохранил его числом 1, простая смена типа на текст не узнает, сколько нулей было раньше. Дополнение до фиксированной длины допустимо только при подтверждённом правиле кодирования.

Для текстового ключа используйте удаление краевых пробелов — Trim; для непечатаемых управляющих символов предусмотрен Clean. Соответствующие функции M называются Text.Trim и Text.Clean. Это разные операции: не следует ожидать, что удаление краёв автоматически исправит все символы внутри значения. После очистки убедитесь, что в нашем наборе осталось именно 001 и 002.

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

Числа и даты: обратить внимание на локаль

В Amount должны оказаться числа, а не строки, похожие на суммы. При необходимости выберите изменение типа с использованием локали и укажите формат, соответствующий источнику. Для показанных «1 200,50» и дат вида «01.11.2026» в упражнении используем русскую локаль. Это не правило для всех файлов: англоязычная выгрузка может использовать другие разделители.

Для денежных значений выберите подходящий числовой тип; в нашем примере используем Fixed Decimal/Currency с фиксированными четырьмя знаками после десятичного разделителя. Date преобразуйте в дату. Посмотрите, на каком шаге появляются ошибки, вместо удаления всех ошибок разом. Если неверно выбрана локаль, массовое удаление может уничтожить корректные строки.

Проверьте контрольные суммы до объединения: SalesA должен дать две строки и 1 550,50, SalesB — одну строку и 900. Если тип Date изменён автоматически ещё до очистки, оцените правильность этого раннего шага: автоматическое распознавание не является подтверждением смысла данных.

Append добавляет строки, Merge присоединяет соответствия

Для двух таблиц продаж используйте Append Queries, при желании — как новый запрос. Это операция добавления строк в общий набор. Power Query сопоставляет колонки по именам, а не по тому, где они визуально стоят. Если забыть переименовать client_code, результат получит разные колонки с пустыми значениями вместо единого ClientID.

После добавления SalesA и SalesB должно быть три заказа, общая сумма 2 450,50. Теперь нужен менеджер из другой таблицы, поэтому используем Merge Queries:

1. В качестве левой таблицы выберите объединённые продажи, правой — Managers.
2. Выделите ClientID в обеих таблицах. Тип ключа должен быть согласован — текст.
3. Для нашего требования «оставить все продажи» выберите Left Outer join.
4. После объединения раскройте вложенную колонку справочника и оставьте Manager.

Результат: A1 и B1 получают Ирину, A2 — Павла. Если соответствие не найдено, у продажи останется пустой менеджер; это повод проверить ключ и справочник. Inner join мог бы убрать такую продажу из результата, поэтому не выбирайте его только ради исчезновения пустых ячеек.

Почему после Merge внезапно становится больше строк

Проверьте уникальность ClientID в справочнике до объединения. Если для 001 там два соответствия, две продажи этого клиента при раскрытии получат по две строки. Вместе с заказом A2 окажется пять строк вместо трёх. Сумма вырастет не из-за новых продаж, а из-за кратности соединения.

Решение зависит от смысла: исправить ошибочный дубль справочника, уточнить ключ или выбрать нужное соответствие по отдельному бизнес-правилу. Произвольное удаление повторов после Merge может спрятать причину и оставить неверного менеджера. Аналогично одинаковые OrderID не всегда означают одинаковые записи: сначала подтвердите, что именно считается уникальным заказом.

Документация Power Query отдельно предупреждает о чувствительности операций с дубликатами к регистру и о том, что нельзя безусловно рассчитывать на сохранение «первой» нужной строки. Если порядок важен для выбора версии записи, нужен явно спроектированный способ отбора, а не надежда на текущее отображение.

Загрузить результат и повторить обновление

Завершите редактор через Закрыть и загрузить либо вариант загрузки в выбранное место. Сохраните итоговую таблицу отдельно от входных данных. Измените тестовое значение в исходном файле, сохраните его и выполните Данные → Обновить всё. Это проверяет весь путь: источник, шаги и выгрузку.

Для внешнего подключения доступны свойства обновления, например обновление при открытии или через интервал, если этот вариант поддерживается подключением. Для проверки интервального обновления в нашем опыте оставьте Excel с открытой книгой и убедитесь, что новое значение действительно появилось в результате. Условия автоматизации за пределами этого опыта требуют отдельной проверки. Если используется фоновое обновление, дождитесь его окончания перед сравнением результата.

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