Power Query в Excel: загрузка, очистка и объединение данных
Повторяемая подготовка данных в Power Query: CSV и папка, типы и пропуски, разделение и unpivot, merge и append, группировка и проверка итогов.
Содержание статьи
Каждое утро приходит новый CSV с бронированиями. Вчера аналитик скопировал его в книгу, исправил даты и пересчитал сводную; сегодня повторил то же самое, но забыл убрать строку итога. Power Query нужен, когда шаги загрузки и очистки должны повторяться. Ниже построим такую цепочку на учебной выгрузке авиабронирований и проверим каждый результат SQL-расчётом на тех же строках. Названия команд и функции M сверены с документацией Microsoft 27 сентября 2026 года; код M здесь не запускался.
Коротко: что сделать в Power Query
Подключите CSV или таблицу Excel через Data → Get Data, проверьте предварительный просмотр и откройте редактор Transform Data. В запросе закрепите типы полей, обработайте пропуски по явному правилу, затем добавьте необходимые преобразования. Если файлы приходят порциями, добавьте строки друг под другом; если к строкам нужен справочник, соедините таблицы по ключу. В конце сгруппируйте данные и загрузите результат в лист или модель.
Главное преимущество — не количество кнопок, а воспроизводимый список Applied Steps. При следующем файле вы обновляете источник и повторяете ту же логику. Но Refresh не гарантирует правильность: если изменились имена колонок, разделитель или зерно данных, запрос может дать ошибку либо правдоподобный неверный итог. Поэтому рядом нужна контрольная сумма и число строк.
В учебном примере контроль до и после подготовки — 23 496 уникальных броней на 1 884 807 100 ₽ за полные дни 3–6 августа 2026 года. Это стоимость оформленных броней, не оплаченная выручка. Любое изменение числа строк в шаге очистки или объединения должно иметь объяснение.
Один набор файлов для трёх видов преобразования
Скачайте основной CSV и XLSX с типизированными значениями. В обоих — одна строка на book_ref, московская дата book_date, целочисленная сумма total_amount, число билетов и группа ticket_bucket. Имён, адресов и контактов пассажиров нет. Источник — производная учебной базы Postgres Professional под MIT, даты сдвинуты на 3283 дня.
Для Append нужны две части того же CSV: 3–4 августа и 5–6 августа. Для Merge скачайте справочник типов. Для Unpivot — дневную широкую таблицу. Все вспомогательные файлы произведены из главного среза, а не придуманы вручную; исключение — русские подписи двух групп в справочнике.
Назовите запросы Bookings, Bookings0304, Bookings0506, TicketBuckets и DailyWide: так имена в примерах M будут соответствовать вашим запросам. Не загружайте одновременно основной CSV и две его части в одну фактовую таблицу: вы получите 46 992 строки, то есть удвоите данные. Части — альтернативный способ собрать тот же исходный набор. XLSX пригодится, если вы хотите пройти шаги без настройки текстового импорта. Лицензия и происхождение данных записаны в NOTICE.
| Файл | Зерно | Строк |
|---|---|---|
| Основной CSV / Bookings в XLSX | одна бронь | 23 496 |
| 03–04 и 05–06 | та же бронь, два непересекающихся периода | 11 478 + 12 018 |
| ticket-buckets.csv | одна группа | 2 |
| daily-wide.csv | один день | 4 |
Загрузка: CSV, таблица Excel или папка
Для одиночного CSV выберите Data → Get Data → From File → From Text/CSV, укажите файл и перейдите в Transform Data. Сразу проверьте, что заголовки стали названиями пяти колонок, разделитель — запятая, а русские подписи читаются в UTF-8. Если открываете готовый XLSX, выберите таблицу Bookings, а не весь лист с текстовыми примечаниями над ней. В результате должно быть 23 496 строк.
Папка подходит, если туда регулярно складывают однородные CSV. Подключение From Folder и Combine & Transform Data объединяет файлы, используя выбранный образец. Это удобно, но опасно, если в папку попал черновик, дубликат или файл с другой схемой. Для упражнения поместите туда только две непересекающиеся части, а не полный CSV вместе с ними.
Путь к файлу — часть рабочего контракта. При передаче книги коллеге старый локальный путь может перестать существовать. Перед публикацией укажите, где лежит исходник, кто его обновляет и какое имя файла ожидается. Сначала проверьте успешную загрузку и итоги, а потом стройте график.
Шаги запроса: порядок меняет результат
Редактор хранит последовательность Applied Steps. Типичный путь здесь: Source → заголовки → типы → проверка пропусков → нужное объединение → группировка → загрузка. Перестановка не всегда безобидна. Если до смены типа сравнивать даты как строки в неоднородных форматах, фильтр может отобрать не те дни. Если до устранения дублей соединить справочник с повторяющимися ключами, строки умножатся.
Дайте каждому важному шагу имя по действию: «Типы и календарь», «Проверка ключей», «Справочник групп». Общий шаг «Changed Type» через полгода трудно связать с решением. Удаляйте лишние автоматические шаги, но не стирайте шаг, на который опирается следующий. После каждого структурного изменения смотрите число строк и сумму, а не только первые пять строк предварительного просмотра.
Power Query может показывать выборку данных в превью. Превью полезно для формы таблицы, но не является проверкой всех 23 496 строк. Контрольный итог после Load нужен отдельно; число 1 884 807 100 ₽ получено из всей базы и CSV, а не из видимой верхушки редактора.
Типы данных: дата и число должны ими оставаться
Назначьте book_ref текстом, book_date датой, total_amount и ticket_count целыми числами, ticket_bucket текстом. В M это выражается через документированную Table.TransformColumnTypes. Для даты ISO 2026-08-03 преобразование однозначно, но региональные форматы вроде 03/08/2026 требуют указания локали. Если формат источника плавает, зафиксируйте локаль в шаге, а не надейтесь на настройки компьютера читателя.
Проверка результата на нашем срезе: минимум даты 03.08.2026, максимум 06.08.2026, 23 496 целых сумм, общий итог 1 884 807 100 ₽. Идентификатор book_ref не преобразуйте в число — коды могут начинаться с нуля и содержать буквы. Символ ₽ держите в формате отображения, не в текстовой ячейке суммы.
Если после смены типа появилось несколько ошибок, не удаляйте строки сразу. Сначала найдите образец значения и выясните, изменилась ли схема выгрузки. Массовое Remove Errors может сделать итог «зелёным», потеряв реальные брони. Для регулярного процесса запишите допустимый тип каждой колонки рядом с источником.
Table.TransformColumnTypes(Source, {{"book_ref", type text}, {"book_date", type date}, {"total_amount", Int64.Type}, {"ticket_count", Int64.Type}})Синтаксис по Microsoft Learn; результат в статье пересчитан SQL.
Пропуски: Fill Down и Remove Rows не взаимозаменяемы
В основном срезе пропусков в ключе, дате и сумме нет: проверка SQL вернула 0, 0 и 0. Поэтому любое заполнение вниз здесь должно оставить итог неизменным. Это важный учебный результат: шаг очистки не обязан что-то менять. Если после него число броней уменьшилось, вы удалили данные без основания.
Fill Down имеет смысл, когда экспорт группирует строки под одним заголовком и значение категории записано только в первой строке группы. Но для book_ref и total_amount это опасно: заполнение пустого ключа предыдущим создаст дубликат, а заполнение суммы — придуманную стоимость. Пустую сумму лучше отправить в карантинный список и разобраться с источником. Remove Blank Rows подходит для полностью пустых строк между записями, но не для строки с частично пустыми полями.
Запишите правило до кнопки: какое поле может быть пустым, откуда берётся значение для заполнения и сколько строк разрешено потерять. В этом примере допустимая потеря — ноль. После каждого действия пересчитайте COUNT строк, COUNT уникальных ключей и SUM суммы. Иначе отчёт может выглядеть корректно, только потому что плохие записи молча исчезли.
| Поле | Пустых значений | Действие в упражнении |
|---|---|---|
| book_ref | 0 | не заполнять |
| book_date | 0 | не удалять строки |
| total_amount | 0 | не подставлять сумму |
Разделение столбца: когда оно помогает, а когда мешает
Иногда поставщик присылает одну текстовую колонку вроде «регион|канал». В редакторе её можно разделить по разделителю на два поля. В нашем файле такого смешанного бизнес-поля нет. Для безопасной проверки механики возьмите текст book_date из CSV и временно разделите его по дефису на год, месяц и день: для первой строки получится 2026, 08, 03. После упражнения вернитесь к одной дате типа date — три текстовых фрагмента хуже для календарной группировки.
Документированные функции M для такого шага — Table.SplitColumn и Splitter.SplitTextByDelimiter. Они не догадываются о значении частей; вы сами называете выходные колонки. Перед разделением проверьте, что каждая строка имеет ожидаемое число частей. Если поставщик внезапно изменит формат даты, результат может оказаться не в тех колонках.
На всех 23 496 строках ISO-дата содержит два дефиса, поэтому после разделения должно остаться 23 496 строк, а год у всех равен 2026. Это подтверждено эквивалентным SQL split_part на том же срезе. Разделение меняет колонки, но не должно размножать или удалять брони.
Table.SplitColumn(Source, "book_date", Splitter.SplitTextByDelimiter("-"), {"year", "month", "day"})Делайте этот шаг до преобразования book_date в date; затем вернитесь к дате.
Unpivot: верните широкую таблицу к длинному виду
Файл daily-wide.csv содержит четыре дня и две денежные колонки: one_ticket_amount и multi_ticket_amount. Такой вид удобен для просмотра, но неудобен, если завтра появится третья группа. Unpivot превращает названия колонок в значения поля «тип», а их суммы — в поле «сумма». На выходе четыре дня × две группы = восемь строк.
Выберите book_date как неподвижный столбец и команду Unpivot Other Columns. В M это Table.UnpivotOtherColumns с одним ключом. Для 3 августа появятся две строки: 213 996 900 ₽ для одной билета и 238 161 400 ₽ для нескольких. Сумма всех восьми строк останется 1 884 807 100 ₽. Результат сверен SQL через UNION ALL двух денежных колонок.
Не применяйте Unpivot к total_amount вместе с двумя частями: вы получите общий итог и части как три самостоятельные суммы и удвоите деньги при следующей агрегации. Для упражнения сначала оставьте только дату и две колонки групп. Если новая категория добавится новым столбцом, «Unpivot Other Columns» включит её автоматически — проверьте, что это соответствует контракту источника.
Table.UnpivotOtherColumns(DailyWide, {"book_date"}, "ticket_bucket", "amount")Результат: 8 строк, общая сумма 1 884 807 100 ₽.
| Дата | Группа | Сумма, ₽ |
|---|---|---|
| 03.08 | one_ticket_amount | 213 996 900 |
| 03.08 | multi_ticket_amount | 238 161 400 |
| 04.08 | one_ticket_amount | 217 386 500 |
| 04.08 | multi_ticket_amount | 250 280 100 |
Merge: добавьте подпись, не умножив брони
Справочник ticket-buckets.csv содержит две строки: 1 → Один билет, 2+ → Несколько билетов. В редакторе выберите Merge Queries, ключ ticket_bucket в обоих запросах и Left Outer — все брони должны остаться. После соединения разверните только ticket_label. Это аналог SQL LEFT JOIN, где правая сторона должна содержать не более одной строки на ключ.
Контроль до Merge: 23 496 строк и 1 884 807 100 ₽. Контроль после: ровно те же 23 496 строк и та же сумма, пустых подписей — ноль. Если в справочнике случайно появятся две строки 2+, каждая многоместная бронь продублируется. Power Query не обязан предупреждать, что бизнес-итог вырос: это ваша проверка зерна.
Используйте объединение именно для добавления атрибута к строке, а не для склейки двух периодов друг под другом. И не пытайтесь лечить размножение строк кнопкой Remove Duplicates после соединения: она может удалить реально разные записи. Сначала обеспечьте уникальность справочника и объясните ожидаемое число совпадений.
Table.NestedJoin(Bookings, {"ticket_bucket"}, TicketBuckets, {"ticket_bucket"}, "lookup", JoinKind.LeftOuter)После шага раскройте lookup.ticket_label: строк должно остаться 23 496, пустых подписей — ни одной.
Append: сложите периоды строками, не столбцами
Файлы bronirovaniya-03-04.csv и bronirovaniya-05-06.csv содержат одинаковые пять колонок и непересекающиеся даты. Append Queries добавит строки второго под первым: 11 478 + 12 018 = 23 496 броней. Суммы частей — 919 824 900 ₽ и 964 982 200 ₽, вместе 1 884 807 100 ₽. Это аналог SQL UNION ALL, а не JOIN.
Microsoft указывает, что Append сопоставляет поля по именам, а не по позиции. Поэтому total_amount и ticket_count не перепутаются при перестановке колонок, но опечатка в имени создаст отдельное поле и пустоты. После добавления проверьте набор колонок, типы и сумму. Если файлы пересекаются по датам, простое Append сохранит повторы; здесь части разделены точно по полуоткрытым интервалам.
Для регулярной папки добавьте фильтр на расширение и соглашение об имени файла. Архивируйте или исключайте уже обработанные файлы, если новые выгрузки иногда повторяют старые строки. Дедупликация только по дате неверна: в один день тысячи броней. Надёжный ключ проверки — book_ref.
Table.Combine({Bookings0304, Bookings0506})Эквивалент SQL UNION ALL: 23 496 строк и 1 884 807 100 ₽.
Группировка: из броней в дневную витрину
После очистки и Merge оставьте зерно «одна бронь», а отдельным запросом Group By создайте витрину «дата × группа». В расширенном режиме выберите book_date и ticket_bucket как ключи, Count Rows для числа броней и Sum total_amount для суммы. На выходе восемь строк. Дневные и групповые итоги должны складываться в общий контроль.
Для 3 августа получится 3 731 бронь с одним билетом на 213 996 900 ₽ и 1 934 многоместные брони на 238 161 400 ₽. Вместе это 5 665 броней и 452 158 300 ₽. SQL-запрос с GROUP BY по тем же полям дал ровно такие результаты. Если после группировки пропал один тип, проверьте, не остался ли фильтр в предыдущем шаге.
Мера «средняя сумма брони» считается из суммы и числа броней после группировки. Не усредняйте восемь средних значений, чтобы получить среднее за четыре дня: группы имеют разное число строк. Сохраняйте сумму и количество рядом, тогда любой потребитель витрины сможет вычислить взвешенную среднюю без доступа к деталям.
Table.Group(Bookings, {"book_date", "ticket_bucket"}, {{"bookings", each Table.RowCount(_), Int64.Type}, {"amount", each List.Sum([total_amount]), Int64.Type}})Синтаксис функций — Microsoft Learn; восемь значений проверены SQL.
| Группа | Броней | Сумма, ₽ |
|---|---|---|
| 1 | 3 731 | 213 996 900 |
| 2+ | 1 934 | 238 161 400 |
| Обе | 5 665 | 452 158 300 |
Куда загрузить результат: лист или модель
Для учебного дашборда загрузите восьмистрочную витрину в отдельную таблицу на листе и постройте сводную или график. Так читатель может открыть детали подготовки. Если данных больше и несколько таблиц связаны ключами, Excel позволяет загрузить запрос в Data Model через Close & Load To; тогда модель и меры становятся отдельным слоем. Но не отправляйте туда таблицу только потому, что кнопка доступна.
Выберите место загрузки по вопросу: лист удобен для просмотра результата и простых сводных, модель — для связей и повторного использования нескольких таблиц. В обоих случаях исходный запрос и список шагов должны остаться доступны аналитику. Пометьте, какая таблица детальная, а какая уже агрегирована: соединять их потом без учёта зерна опасно.
Если цель — один график по четырём дням, хватит восьми строк на листе. Если требуется единая метрика для многих пользователей с разными правами, архитектура выходит за пределы одной книги. Тогда сохраните проверенную логику SQL и перенесите её в общий слой данных.
Refresh и пять проверок после нового CSV
Нажмите Refresh All после замены или добавления источника. Затем проверьте: файл найден, набор колонок тот же, типы не сломались, минимум и максимум дат ожидаемы, строк и уникальных ключей столько же, сколько событий. Для нашего неизменного упражнения пятая проверка — сумма 1 884 807 100 ₽. В рабочем потоке сравните текущий итог с выгрузкой источника за тот же период.
Пять ошибок здесь особенно дороги. Текстовая сумма даст неправильный агрегат; заполнение пустого book_ref вниз создаст ложный дубль; Merge с неуникальным справочником умножит деньги; Append полного файла и частей удвоит все записи; Unpivot общего итога вместе с его составляющими удвоит сумму. Шестая ошибка — Refresh по старому пути создаст впечатление свежего дашборда, хотя данные не изменились.
Сделайте контрольные значения видимыми в отдельном небольшом блоке: число строк, уникальные ключи, сумма, пропуски и дата источника. Они не отвечают на бизнес-вопрос, зато показывают, можно ли доверять ответу. Когда контроль не сошёлся, остановите публикацию нового отчёта и оставьте прежнюю проверенную версию с явной датой.
Частые вопросы и следующий шаг
«Power Query меняет исходный CSV?» Нет, шаги применяются в запросе; исходный файл остаётся источником. «Чем Merge отличается от Append?» Merge добавляет колонки по ключу, Append добавляет строки одинаковой схемы. «Почему после Merge стало больше строк?» Проверьте уникальность ключа в правой таблице и число совпадений; у нас справа ровно две уникальные группы.
«Почему после обновления пропала дата?» Часто новый файл имеет другую структуру или дата распознана как текст/ошибка; проверьте шаг типов и локаль. «Можно ли загрузить результат только в модель?» Да, но выбирайте это под дальнейшие связи и меры, а не по привычке. Если нужен видимый отчёт на листе, загрузите проверенный агрегат туда.
Дальше соберите дашборд в Excel на тех же строках и сравните итог карточки с нашей суммой. Если исходные данные живут в базе, повторите группировку и JOIN в тренажёре «SQL с нуля»: подготовка источника и проверка зерна остаются обязательными независимо от интерфейса.
Материалы по теме
Как сделать дашборд: от вопроса бизнеса до публикации
Пошагово собираем дашборд на проверенных данных: решение и читатель, SQL для KPI, динамика, разрез, проверка чисел, доступы и дата обновления.
Примеры дашбордов: продажи, продукт, маркетинг и руководитель
Шесть макетов дашбордов под разные решения: кто смотрит, какие метрики считать, где показать детали и какие ошибки исказят вывод. Два примера — на проверенных данных.
Сводные таблицы в Excel: как сделать и что анализировать
Создаём сводную таблицу на 23 496 учебных бронированиях: поля, суммы и доли, группировка дат, срезы, обновление и проверка ошибок.