Как объединять и преобразовывать данные в Power Query для BI-аналитики

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

Разберём авторский пример на условных заявках образовательного сервиса. Сначала добавим строки второго месяца, затем приведём типы, отберём нужные статусы и присоединим названия школ. Итогом станет проверяемая таблица, а не просто запрос без красных ошибок.

Append и merge отвечают на разные вопросы

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

Merge сопоставляет строки по ключам и добавляет данные из другой таблицы. В нашем случае в заявке есть school_id, а в справочнике — соответствующее название. Это горизонтальное обогащение. Выбор внешне похожей кнопки без понимания этих двух операций даёт либо пустые значения, либо неправильное количество строк.

Таблица	Столбцы	Назначение
Январь	order_id, school_id, amount, status	Две строки заявок
Февраль	Те же четыре столбца	Три новые строки
Школы	school_id, school_name	Одна строка на школу

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

Подключаем реальные книги в Power BI

В Power BI Desktop начните с получения данных и выберите подключение Excel Workbook. Укажите книгу января. В Navigator выберите нужную таблицу или лист и нажмите Transform Data, чтобы открыть преобразования до загрузки модели. Повторите подключение для книги февраля и справочника школ. В редакторе должны появиться отдельные запросы, которые можно осмысленно переименовать.

Предпочтите явно определённые таблицы книги, если источник их предоставляет: выбор листа и выбор таблицы могут давать разную область данных. Проверьте заголовки и лишние строки до приведения типов. В нашем коде January, February и Schools — замена этих трёх подключений, позволяющая отдельно воспроизвести преобразования.

Не объединяйте файлы только потому, что Navigator смог их открыть. Сначала убедитесь, что каждый запрос описывает одну и ту же сущность и использует согласованные названия. После этого выполните Append для двух месячных запросов, а Merge со справочником — для уже нормализованного результата.

Пустые значения и повторы очищают по правилу

В меню фильтра столбца команда Remove empty исключает null и пустые значения. Для нашего order_id это полезно как способ найти и отдельно обработать некорректные строки; для school_name после LeftOuter такая команда скроет проблему справочника вместе с заявкой. Сначала решите, какие строки должны оставаться в итоговом наборе.

Remove duplicates сравнивает выбранные столбцы. Если выделен только order_id, вы задаёте другое правило, чем при выделении всех полей. Power Query учитывает регистр и не гарантирует сохранение первой строки среди дублей. Поэтому для истории статусов нужны явные правила выбора актуальной записи, а для простого удаления одинаковых строк — предварительная проверка, что это действительно один и тот же факт.

Данные для самостоятельного опыта

В январе есть A01/s1/1200.50/paid и A02/s2/900.00/paid. В феврале — A03/s1/500.00/paid, A04/s3/700.00/paid и A05/s2/100.00/refunded. Значения пока текстовые. Справочник содержит s1 = «Альфа» и s2 = «Бета», но не содержит s3. Пропуск оставлен намеренно, чтобы проверить качество соединения.

Для воспроизведения не нужны внешние файлы: следующий M-запрос создаёт учебные таблицы внутри Power Query. В Power BI Desktop откройте редактор Power Query, создайте пустой запрос и вставьте код в Advanced Editor. Это пример для выполнения в выбранном приложении; фактический запуск Power Query в статье не заявляется.

let
    January = #table(
        {"order_id", "school_id", "amount", "status"},
        {{"A01", "s1", "1200.50", "paid"},
         {"A02", "s2", "900.00", "paid"}}
    ),
    February = #table(
        {"order_id", "school_id", "amount", "status"},
        {{"A03", "s1", "500.00", "paid"},
         {"A04", "s3", "700.00", "paid"},
         {"A05", "s2", "100.00", "refunded"}}
    ),
    Schools = #table(
        type table [school_id = text, school_name = text],
        {{"s1", "Альфа"}, {"s2", "Бета"}}
    ),
    Appended = Table.Combine({January, February}),
    Typed = Table.TransformColumnTypes(
        Appended,
        {{"order_id", type text}, {"school_id", type text},
         {"amount", type number}, {"status", type text}},
        "en-US"
    ),
    Paid = Table.SelectRows(Typed, each [status] = "paid"),
    Joined = Table.NestedJoin(
        Paid, {"school_id"}, Schools, {"school_id"},
        "school", JoinKind.LeftOuter
    ),
    Expanded = Table.ExpandTableColumn(
        Joined, "school", {"school_name"}, {"school_name"}
    )
in
    Expanded

Типы задаём до расчётов и соединений

Table.Combine добавляет строки исходных таблиц. Затем Table.TransformColumnTypes переводит amount в число с культурой en-US: в наших исходных строках десятичный разделитель — точка. Для файла с другим форматом нужно выбрать соответствующую культуру, а не менять её вслепую до исчезновения ошибки.

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

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

Соединение не должно незаметно размножать заявки

Table.NestedJoin создаёт вложенную колонку school с совпадающими строками справочника. Мы явно выбрали LeftOuter, чтобы сохранить все отобранные заявки, включая A04 без названия школы. Table.ExpandTableColumn раскрывает только school_name, не добавляя второй одноимённый ключ.

Если в справочнике для s1 окажутся две строки, при раскрытии каждая заявка этой школы получит два соответствия. У нас A01 и A03 начнут повторяться, а сумма после merge вырастет. Это нарушение ожидаемой кардинальности, даже если запрос успешно завершился.

До merge проверьте правило «одна строка на school_id» на стороне справочника. Если у школы несколько названий во времени, решите, какое требуется: текущее или действовавшее на дату заявки. Случайное удаление дубля не выбирает правильную историческую запись.

Inner join оставил бы только совпадения и скрыл A04. Иногда такое соединение нужно по задаче, но оно не подходит для молчаливого исправления неполного справочника. Отсутствующее соответствие должно быть видно в контроле качества, а не исчезать вместе с фактом.

Проверяем числа на каждом переходе

Этап	Ожидаемый результат примера	Что означает отклонение
После append	5 строк, четыре исходных столбца	Проверить файлы, названия и повторную загрузку
После отбора paid	4 строки	Проверить значения status и правило отбора
После merge и раскрытия	4 строки, у A04 school_name = null	Лишние строки указывают на несколько совпадений; потеря — на другое соединение
Сумма amount у paid	3300,50	Сверить тип, фильтр и размножение при соединении

По школам суммы составят: s1 — 1700,50, s2 — 900,00, s3 — 700,00. Последняя группа не должна теряться только из-за пустого названия. Для отчёта можно показать понятную метку отсутствующего справочника, сохранив исходный код для исправления.

Эти суммы — сумма строк со статусом paid в учебной таблице. Мы не называем её бухгалтерской выручкой или чистым денежным потоком: для таких показателей нужны правила возвратов, дат признания и состава операций. Строка A05 со статусом refunded исключена выбранным фильтром; это ещё не полноценный расчёт возвратов.

Как перенести опыт на ежемесячные файлы

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

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

При обновлении важен не только успешный refresh. Сравните количество строк, сумму выбранного показателя, пропуски соответствий и повторы ключей с ожидаемой картиной. Так append и merge становятся частью проверяемого процесса подготовки BI-данных, а неожиданный скачок на диаграмме получает объяснимый источник.