Power Query с нуля: как собрать обновляемый отчёт из нескольких таблиц

Какую часть работы берёт на себя Power Query

Power Query готовит данные для дальнейшего анализа. Он подключается к источнику, выбирает нужную таблицу, меняет типы столбцов, очищает значения и объединяет наборы данных. Результат можно загрузить в Excel, а затем использовать для формул, сводной таблицы или другого отчёта.

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

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

Из каких файлов соберём учебный отчёт

Предположим, отдел получает две выгрузки продаж — из Москвы и Санкт-Петербурга. В каждой есть дата, код товара, количество и выручка. Отдельно хранится справочник с кодами, названиями и категориями товаров. Числа ниже учебные: по ним будет удобно проверить итог.

Нам нужна одна таблица со всеми четырьмя строками и названиями товаров. Контрольные значения уже можно записать: продано 7 единиц, выручка составляет 5 900. Они помогут заметить ошибку, даже если готовая таблица будет выглядеть аккуратно.

В программе «Power BI и Power Query» от SF Education отдельно рассматриваются подключение к источникам, очистка, объединение запросов и обновление данных. Затем подготовка таблиц связывается с моделью и визуализациями в Power BI. Это подходящий следующий шаг, если вы хотите пройти весь процесс от выгрузки до отчёта и закрепить его на учебных проектах.

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

Подготовьте исходные таблицы

В каждой выгрузке должна быть одна строка заголовков. Данные начинаются сразу под ней, а каждая строка описывает одну запись. Уберите из выбранного набора декоративные надписи и промежуточные итоги: иначе они могут попасть в обработку наравне с продажами.

Названия столбцов в двух выгрузках должны обозначать одинаковые сущности. Если в одной таблице стоит «Код», а в другой «Артикул», заранее определите общее название. Не объединяйте два поля только потому, что в них похожие значения: сначала убедитесь, что они действительно описывают один и тот же объект.

Для учебного файла можно выделить исходные записи и оформить их как таблицу Excel через команду «Форматировать как таблицу». Дайте таблицам понятные имена, например «ПродажиМосква» и «ПродажиСПб». Название будет видно при выборе источника и поможет не перепутать данные с уже готовым отчётом.

Справочник товаров проверьте отдельно. В нашем случае на один код должна приходиться одна строка с названием. Если код A101 встречается дважды с разными названиями, это вопрос к устройству справочника. Объединение запросов не решит, какое название считать правильным.

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

В настольном Excel для Windows работа обычно начинается на вкладке «Данные»: можно получить данные из книги или открыть выбранную таблицу в редакторе Power Query. Названия пунктов могут различаться между версиями. В справке Microsoft по Power Query описана работа с запросами и их обновлением.

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

Теперь проверьте тип каждого столбца. Дата должна распознаваться как дата, количество — как число, код — как текст, если это идентификатор. Текстовый тип особенно полезен для кодов с ведущими нулями: превращение «00125» в число 125 может испортить сопоставление со справочником.

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

Очистите значения и сохраните понятный порядок шагов

Начните с простых преобразований: удалите полностью пустые строки, оставьте нужные столбцы, приведите их названия к единому виду. Для текстовых ключей проверьте лишние пробелы. Внешне значения «A101» и «A101 » могут выглядеть одинаково, но мешать точному сопоставлению.

Затем посмотрите, какие шаги уже записаны в запросе. Дайте важным шагам понятные названия: «Оставлены столбцы продаж», «Проверены типы», «Убраны пробелы в коде». Через месяц такой список легче объяснить коллеге, чем последовательность из нескольких похожих технических названий.

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

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

Добавьте строки второго подразделения

Когда обе выгрузки приведены к одному виду, нужно поставить их строки друг под другом. В Power Query для этого используется добавление запросов. Оно отличается от соединения со справочником: сейчас мы увеличиваем число записей, сохраняя общий набор столбцов.

Выберите команду добавления и укажите две подготовленные таблицы. Проверьте результат: должны присутствовать строки обоих городов, а данные одного смысла должны попасть в общие столбцы. Различие между добавлением и слиянием описано в документации Microsoft об объединении запросов.

Для нашего маленького примера итог содержит четыре строки. В реальной работе можно заранее записать: первая выгрузка — 600 строк, вторая — 400. После простого добавления ожидаются 1 000 строк. Если получилось другое число, вернитесь к предыдущим шагам и выясните, где оно изменилось.

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

Присоедините справочник по коду товара

В справочнике нашего примера три строки: A101 — «Лампа», A102 — «Подставка», A103 — «Блокнот». Нам нужно, чтобы каждая продажа сохранилась, а рядом появилось название соответствующего товара. Для этого выбираем слияние по столбцу «Код» и вариант соединения, сохраняющий все строки основной таблицы продаж.

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

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

Проверьте и обратную ситуацию: после раскрытия справочника строк стало больше. Возможная причина — несколько совпадений на один код. Например, одна продажа A101 соединится с двумя строками справочника A101. Поэтому после слияния снова сравните число продаж и контрольную сумму.

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

Загрузите подготовленную таблицу на отдельный лист. Назовите его так, чтобы было ясно: это результат запроса. Исходные файлы, промежуточные таблицы и готовый отчёт лучше различать уже по именам — особенно если книгу будут открывать несколько сотрудников.

Сверьте четыре контрольных пункта: в таблице остались все продажи, названия найдены для ожидаемых кодов, количество равно 7, выручка равна 5 900. Красивый вид таблицы не заменяет эту проверку: ошибка соединения может дать вполне убедительно оформленный результат.

Теперь измените учебный источник. Например, добавьте ещё одну продажу A102 с количеством 2 и выручкой 1 600. После сохранения источника и обновления запроса ожидаются пять строк, 9 единиц и выручка 7 500. Если результат изменился именно так, вы проверили не только первую сборку, но и повторное применение инструкции.

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

Что делать, если в следующем месяце всё сломалось

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

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

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

Если после подготовки данных хочется перейти к модели и интерактивным отчётам, вернитесь к курсу SF Education «Power BI и Power Query». В нём эти этапы входят в одну программу. Сопоставьте её с задачей, которую вы только что решили: какие шаги уже понятны, где нужна практика и насколько важна проверка вашего проекта.