Merge или Append в Power Query: как правильно соединять справочники и операции
Append добавляет строки нескольких однотипных таблиц друг под другом, а Merge присоединяет данные из другой таблицы по совпадающему ключу. Если выбрать неверную операцию или не проверить уникальность ключа, итоговая таблица получит пустые значения, лишние столбцы либо незаметно размноженные строки.
Главная разница Merge и Append
В официальной документации Microsoft операция Append объединяет содержимое таблиц по строкам и формирует общий набор столбцов. Операция Merge соединяет две таблицы по совпадающим значениям одного или нескольких столбцов.
| Вопрос | Операция | Пример |
|---|---|---|
| Нужно собрать одинаковые операции за месяцы? | Append | Продажи января + февраля + марта |
| Нужно добавить к операции название товара? | Merge | Продажи + справочник товаров по коду |
| Нужно объединить файлы одинаковой формы? | Append | Отчёты филиалов |
| Нужно сопоставить факт и план по ключу? | Merge | Код статьи и период |
Подготовка данных
До объединения приведите таблицы к понятной модели. Для Append названия и смысл одинаковых столбцов должны совпадать. Для Merge ключи должны иметь одинаковый тип данных и одинаковые правила записи.
- удалите лишние пробелы и непечатаемые символы в текстовых ключах;
- приведите к одному регистру, если источник использует разные варианты;
- не смешивайте текстовый код «00125» и число 125;
- проверьте даты и локаль преобразования;
- решите, допустимы ли пустые ключи;
- зафиксируйте уникальность справочника до Merge;
- добавьте столбец источника перед Append, если происхождение строк важно.
Общий обзор загрузки и преобразований есть в материале Power Query в Excel. Здесь сосредоточимся именно на соединении наборов.
Append: добавить строки
Представим три выгрузки продаж с одинаковыми столбцами: дата, документ, товар, количество и сумма. Append создаёт одну таблицу операций. Power Query сопоставляет столбцы по названию, а не по позиции. Если в одном файле столбец называется «Сумма», а в другом «Сумма продажи», появятся два столбца с пустыми значениями в разных строках.
Если каждый месяц появляется новый файл одинакового формата, лучше использовать объединение файлов из папки, а не вручную добавлять запрос в формулу. При этом шаблон преобразования должен устойчиво обрабатывать лишний файл, изменённое название листа и пустую выгрузку.
Merge: присоединить столбцы по ключу
В таблице продаж есть код товара, но нет категории и менеджера. В справочнике одна строка на товар содержит эти признаки. Merge сопоставляет код и создаёт вложенный столбец, из которого раскрывают нужные поля.
Пошагово:
- выберите основную таблицу продаж;
- запустите объединение запросов;
- укажите справочник;
- выберите ключ в обеих таблицах в одинаковом порядке;
- выберите тип соединения;
- оцените количество найденных совпадений;
- раскройте только нужные поля, не дублируя ключ;
- проверьте число строк и пустые значения.
Для составного ключа удерживайте одинаковый порядок столбцов, например «Организация», затем «Код товара». Если выбрать их в разной последовательности, совпадения не найдутся.
Виды соединений Merge
| Соединение | Что остаётся | Когда применять |
|---|---|---|
| Left Outer | Все строки первой таблицы и совпадения второй | Обогатить операции справочником |
| Inner | Только совпавшие строки | Выделить подтверждённые пары |
| Right Outer | Все строки второй и совпадения первой | Редко; когда базовой является правая таблица |
| Full Outer | Все строки обеих таблиц | Сверить два перечня полностью |
| Left Anti | Строки первой без совпадения | Найти операции с отсутствующим справочником |
| Right Anti | Строки второй без совпадения | Найти неиспользованные элементы справочника |
Left Outer — частый вариант, но не автоматический правильный. Для сверки платежей и реестра нужен Full Outer или комбинация антисоединений, чтобы увидеть записи, отсутствующие с обеих сторон.
Почему Merge размножает строки
Если ключ во второй таблице не уникален, одной строке первой соответствуют несколько строк второй. После раскрытия результат увеличится. Например, код товара встречается в справочнике трижды, поэтому каждая продажа этого товара превращается в три строки.
До Merge сгруппируйте справочник по ключу и посчитайте строки. Все значения больше единицы требуют решения: удалить точные дубли, выбрать актуальную запись по дате, агрегировать или использовать составной ключ. Команда «Удалить дубликаты» без правила может скрыть разные реальные записи.
Возможны связи один к одному, многие к одному, один ко многим и многие ко многим. Для обогащения операций обычно ожидается «многие операции к одной строке справочника». Проверка этой гипотезы обязательна.
Как диагностировать несовпадения
После Left Outer отфильтруйте пустые присоединённые значения. Создайте отдельный диагностический запрос Left Anti — он покажет ключи, которых нет в справочнике. Частые причины:
- разный тип данных;
- пробелы и невидимые символы;
- ведущие нули потеряны при преобразовании в число;
- в одном источнике код устарел;
- составной ключ неполный;
- дата и время сравниваются с одной датой;
- пустое значение принято за обычный ключ.
Не заменяйте все несовпадения словом «Не найдено» до разбора: это сделает отчёт удобным визуально, но скроет проблему мастер-данных.
Освоить Power Query и собирать устойчивые модели Excel вместо ручного копирования
Курс «Excel + Google Таблицы с нуля до PRO» развивает навыки подготовки, объединения и анализа рабочих данных. Практика помогает выбирать правильную структуру таблиц и строить обновляемые отчёты с контролем качества.
Пример: операции и справочник
Есть ежемесячные файлы операций и справочник статей затрат. Сначала подготовьте каждый файл и примените Append, чтобы получить единый факт. Затем выполните Merge с уникальным справочником по коду статьи. Последовательность важна: одна нормализованная операция соединения проще десятков одинаковых Merge для каждого месяца.
После загрузки проверьте:
- число строк факта равно сумме источников;
- сумма до и после Merge не изменилась, если связь многие к одному;
- доля ненайденных ключей известна;
- по каждому ключу справочника не больше одной актуальной строки;
- обновление следующего месяца не требует ручной правки шагов.
Что происходит в M-коде
Append обычно создаёт выражение Table.Combine со списком таблиц. Merge использует Table.NestedJoin, после чего вложенный столбец раскрывается через Table.ExpandTableColumn. Понимание названий помогает читать шаги и находить ошибку, даже если код был создан интерфейсом.
Не обязательно писать M вручную, но полезно переименовывать шаги: «Добавлены месяцы», «Соединён справочник товаров», «Раскрыта категория». Так следующий специалист понимает логику без восстановления по формулам.
Типичные ошибки
- использовать Merge там, где нужно сложить однотипные файлы;
- ожидать, что Append сопоставит столбцы по позиции;
- не проверять тип и качество ключа;
- соединять по названию вместо устойчивого кода;
- не замечать связь многие ко многим и удвоение сумм;
- удалять дубли без бизнес-правила;
- раскрывать все поля справочника и раздувать модель;
- не создавать контроль числа строк и суммы.
Append отвечает за вертикальное объединение однотипных наборов, Merge — за сопоставление по ключу и добавление признаков. Надёжный запрос начинается с модели данных и заканчивается контролем строк, сумм, уникальности и ненайденных ключей.
Вопросы и ответы
- Чем Merge отличается от Append в Power Query?
Append добавляет строки таблиц друг под другом и сопоставляет столбцы по названиям. Merge соединяет таблицы по ключу и позволяет добавить столбцы из найденной записи. Это аналоги вертикального объединения и операции join.
- Когда использовать Append?
Когда таблицы содержат один тип объектов и их нужно собрать в общий список: месяцы продаж, операции филиалов, файлы из папки. Перед добавлением выровняйте названия, типы и смысл столбцов.
- Какой вид Merge выбрать для справочника?
Обычно Left Outer: сохраняются все строки операций и добавляются совпадения справочника. После соединения обязательно проверьте пустые результаты и уникальность ключа в справочнике.
- Почему после Merge стало больше строк?
На один ключ основной таблицы найдено несколько строк во второй. Проверьте дубли и кардинальность справочника. Нужно выбрать актуальную запись, агрегировать данные или дополнить ключ, а не удалять строки случайно.
- Почему Power Query не находит одинаковые коды?
Частые причины — разные типы данных, пробелы, невидимые символы, ведущие нули, регистр, устаревший код или неполный составной ключ. Диагностировать несовпадения удобно через Left Anti.
- Можно ли сначала Merge, а потом Append?
Можно, но для одинаковых файлов обычно эффективнее сначала нормализовать и добавить строки, а затем один раз присоединить справочник. Исключение — когда источники требуют разных правил соединения или имеют разные ключи.