Объединить запросы Power Query: как присоединить справочник уроков

Определите, что должно остаться одной строкой

Представим внешний журнал проверки материалов двух курсов. Одна строка означает одно замечание редактора, а отдельный справочник связывает курс и урок с ответственным автором. Нужно получить журнал с именем ответственного, сохранив каждое замечание ровно один раз. Это условные данные для обучения; автоматическая выгрузка такого журнала из Umhub здесь не предполагается.

Сначала сформулируйте правило результата: число замечаний и сумма минут на исправление должны сохраниться. Само соединение не создаёт новую редакторскую работу. Если после него нагрузка выросла, нужно проверить соответствия, прежде чем распределять задачи между людьми.

Для этой операции подходит слияние, или Merge. Добавление запросов Power Query решает другую задачу: собирает однотипные журналы друг под другом. Здесь к каждой существующей записи присоединяется признак из справочника. Назовите итоговый запрос ReviewOwners, чтобы коллеги отличали его от исходных таблиц.

Подготовьте четыре замечания и справочник

Создайте копию рабочей книги. Для контрольной пробы используйте запрос Reviews с полями ID, Course, Lesson, Minutes. Вручную внесите четыре придуманные записи:

• R01, A, L01, 12.

• R02, A, L01, 8.

• R03, B, L01, 10.

• R04, A, L09, 5.

Во втором запросе Owners нужны поля Course, Lesson, Owner. Добавьте строки A, L01, «Ирина»; B, L01, «Олег»; A, L02, «Лена». Ответственного для A, L09 намеренно нет: так можно проверить поведение при отсутствии соответствия.

В ожидаемом результате R01 и R02 получают Ирину, R03 — Олега, R04 остаётся без найденного имени. Записей по-прежнему четыре, сумма Minutes равна 35. Несколько замечаний к одному уроку здесь допустимы: уникальным событием является ID замечания, а не код урока.

В обоих запросах храните Course и Lesson как текст. Не подменяйте L01 числом 1 и не считайте одинаковый внешний вид гарантией одинаковых значений. Для реальной книги заранее согласуйте написание кодов между редактором и автором.

Соедините по курсу и уроку одновременно

В редакторе Power Query откройте «Главная» и выберите «Объединить запросы как новые», в английском интерфейсе Merge Queries as New. Укажите Reviews первой таблицей, Owners второй. Выделите Course, затем с Ctrl поле Lesson в обеих таблицах в одинаковом порядке. Выберите левое внешнее соединение, Left outer.

Обзор слияния Microsoft объясняет выбор нескольких ключей: их порядок и типы данных должны соответствовать. Одинаковые названия столбцов сами по себе не обязательны. Для кодов этой пробы оставьте нечёткое сопоставление выключенным: похожий код не означает тот же урок.

После подтверждения раскройте новый вложенный столбец Owners и оставьте поле Owner. Инструкция Microsoft по левому внешнему соединению описывает сохранение записей первой таблицы и добавление найденных значений второй. При отсутствии пары получается null. В нашей пробе это ожидаемое значение у R04, которое требует уточнения справочника.

Найдите ошибку одного ключа на маленькой пробе

В отдельной тестовой копии измените условие соединения: оставьте только Lesson. Теперь L01 из курса A совпадает и с L01 курса B. После раскрытия Owner каждое из первых трёх замечаний получит по два соответствия. Вместе с R04 выйдет семь строк вместо четырёх.

Посчитайте последствия: минуты первых трёх замечаний учтутся дважды, поэтому сумма станет 65 вместо 35. Это не выросшая нагрузка команды. У R01, R02 и R03 просто появились лишние назначения. Сверка суммы помогает заметить проблему, а проверка ID показывает её происхождение.

Верните составной ключ Course и Lesson. Затем проведите другую независимую пробу: добавьте в Owners вторую запись A, L01 с именем «Павел». При правильном составном ключе результат теперь содержит шесть строк и 55 минут. Дублирование справочника размножает два замечания к этому уроку; выбор нескольких полей не устраняет неоднозначность автоматически.

Разберите неоднозначность до загрузки отчёта

Не удаляйте повторяющиеся строки результата наугад. Сначала выясните, почему у одного урока два ответственных. Возможно, справочник содержит старую версию назначения. Возможно, работа действительно разделена между авторами, но тогда нужна другая модель распределения минут. Соединение не определит это за руководителя курса.

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

У R04 сохраните статус «Ответственный не найден». Не назначайте случайного человека ради отсутствия пустот. До уточнения эту строку можно вынести в отдельный список проверки, сохранив пять минут и исходный ID.

Примите результат по трём контрольным условиям

Перед загрузкой ReviewOwners на лист проверьте четыре уникальных ID, итог 35 минут и правильные имена у R01–R03. Отдельно зафиксируйте одно ненайденное соответствие. Эти условия проверяют количество событий, сохранность чисел и смысл назначения; ни одно не заменяет остальные.

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