Как разобрать двойную шапку отчёта в Power Query

Power-инструменты

Преобразуем отчёт с годами и городами в таблицу для анализа. Восстанавливаем шапку, отменяем свёртывание и сверяем суммы по годам.

5 мин чтения 1 октября 2026 Алексей Борисов

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

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

Учебный источник

Создайте в Excel таблицу с именем RawReport. Её технические заголовки — C1, C2, C3, C4 и C5. Две следующие строки являются исходной шапкой отчёта. Знак ∅ в примере ниже обозначает действительно пустую ячейку — его вводить не нужно.

C1	C2	C3	C4	C5
Банк	2024	∅	2025	∅
∅	Москва	Казань	Москва	Казань
Банк А	10	20	15	25
Банк Б	30	40	35	45

Данные синтетические. В исходном отчёте 2024 год относится к двум соседним городам, 2025 год — к следующим двум. Общая сумма числовых значений равна 220. Это первый контроль, который сохраним после преобразования.

Учебная таблица к статье. Слева исходная двойная шапка, справа восемь ожидаемых записей. Это контрольный образец результата. Снимок листа в LibreOffice Calc. Нажмите, чтобы открыть крупнее.

Восстанавливаем год для каждого столбца

Отделим первые две строки от остальной таблицы. Затем транспонируем этот небольшой фрагмент — столбцы станут строками. В полученном первом столбце годы и пропуски теперь расположены сверху вниз.

Применим заполнение вниз к первому столбцу. Значение 2024 заполнит относящийся к нему пропуск, затем то же произойдёт с 2025. Операция работает с null. Пустую строку или строку из пробелов перед таким шагом потребуется привести к null, если именно так выгружен пропуск. Поведение описано в документации Table.FillDown, а перестановка строк и столбцов — в справочнике Table.Transpose.

Теперь для каждого исходного столбца можно составить имя из двух частей. Получатся 2024|Москва, 2024|Казань, 2025|Москва и 2025|Казань. Первый столбец назовём «Банк». Разделитель выбран так, чтобы он не встречался внутри года или города.

Применяем имена к данным

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

После этого оставим «Банк» как идентификатор и отменим свёртывание остальных столбцов. Получим поля «Банк», «ПериодГород» и «Сумма». Разделим составное поле по символу | на «Год» и «Город».

Если операция отмены свёртывания пока незнакома, её базовый смысл и простой пример есть в отдельном разборе матрицы в Power Query. В нашей задаче дополнительная работа нужна именно для восстановления двух уровней шапки.

Полный пример запроса

Создайте пустой запрос в открытой книге, перейдите в расширенный редактор и вставьте код. Он обращается к таблице RawReport внутри этой книги.

let
    Source = Excel.CurrentWorkbook(){[Name="RawReport"]}[Content],
    Header = Table.FirstN(Source, 2),
    Rotated = Table.Transpose(Header),
    Filled = Table.FillDown(Rotated, {"Column1"}),
    Pairs = List.Skip(Table.ToRows(Filled), 1),
    Names = {"Банк"} & List.Transform(
        Pairs, each Text.From(_{0}) & "|" & Text.From(_{1})
    ),
    Body = Table.Skip(Source, 2),
    Renamed = Table.RenameColumns(
        Body, List.Zip({Table.ColumnNames(Body), Names})
    ),
    Long = Table.UnpivotOtherColumns(
        Renamed, {"Банк"}, "ПериодГород", "Сумма"
    ),
    Split = Table.SplitColumn(
        Long, "ПериодГород",
        Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
        {"Год", "Город"}
    ),
    Result = Table.TransformColumnTypes(
        Split, {{"Год", Int64.Type}, {"Сумма", type number}}, "ru-RU"
    )
in
    Result

Это учебный пример M-кода для указанной структуры. Ожидаемые записи и суммы ниже рассчитаны отдельно. Запуск именно этого запроса в настольном Power Query пока не подтверждён.

Код рассчитан на описанную структуру с двумя строками шапки. После загрузки результата проверьте восемь ожидаемых записей и контрольные суммы.

Как проверить результат

В результате должно быть восемь записей. У Банка А за 2024 год должны находиться Москва с суммой 10 и Казань с суммой 20, за 2025 год — суммы 15 и 25. Для Банка Б ожидаются 30, 40, 35 и 45 соответственно.

Проверьте общий итог 220. Затем сгруппируйте результат по году — для 2024 получится 100, для 2025 получится 120. Такая проверка способна обнаружить ошибку привязки года, которую одна общая сумма не покажет.

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

Проверьте и пропуски в самой области данных. При отмене свёртывания исходные null могут не дать отдельных записей. Ожидаемое число строк поэтому определяется содержимым источника и выбранным правилом работы с пустыми значениями. Базовая операция описана в документации Microsoft по отмене свёртывания.

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

Файлы для самостоятельной проверки

В исходном CSV сохранены два уровня шапки и пропуски в названиях столбцов. Создайте из него таблицу RawReport, как описано выше. Второй CSV содержит восемь ожидаемых записей после преобразования. Оба файла используют синтетические данные.

Скачать исходный пример · Скачать контрольный результат

Сверьте количество записей и суммы по годам. Общий итог равен 220, за 2024 год — 100, за 2025 год — 120. Отдельно проверьте привязку города к году. Одного совпадения общей суммы для такой проверки недостаточно.

Контрольный файл описывает ожидаемый результат. Запуск приведённого M-запроса в настольном Power Query пока не подтверждён. Перед использованием на рабочей выгрузке выполните его на учебной копии и сравните строки.

На корпоративном обучении Excel можно разобрать подготовку отчётности и проверки итогов. Для обсуждения практикума пришлите описание структуры выгрузки без рабочих данных.

Практика на корпоративном обучении

Power Query для подготовки и проверки отчётности. Программа включает учебные задачи, самостоятельный расчёт и сверку результатов.