Power Query: импорт и очистка данных

Запиши ежемесячную ручную работу один раз: импорт из CSV и папок, шаги очистки, объединение таблиц друг под другом (Append), соединение по ключу (Merge) и превращение столбцов в строки (Unpivot).

  • Продвинутый
  • 22 мин
  • 10 вопросов
  • Урок 20 из 27

В этом уроке ты узнаешь

  • Импортировать данные и работать с примененными шагами в редакторе Power Query
  • Очищать пробелы, регистр, типы данных и дубликаты
  • Различать Append и Merge и выбирать тип соединения
  • Превращать «широкую» таблицу в «длинную» для анализа с помощью Unpivot

В начале каждого месяца бухгалтер получает CSV-файлы от трёх филиалов. В именах лишние пробелы, «BAKU», «baku» и «Baku» вперемешку, даты приходят текстом, а продажи по месяцам разбросаны по столбцам. Такая чистка занимает два часа каждый месяц. Power Query записывает эти шаги один раз, а в следующем месяце достаточно нажать Refresh.

Power Query — встроенный в Excel начиная с версии 2016 инструмент «получить, преобразовать, загрузить»; тот же движок работает в Power BI. Он никогда не меняет исходный файл: все преобразования хранятся внутри запроса. Простое правило: подготовка данных (импорт, очистка, объединение) — работа Power Query, а расчёты — работа формул и сводных таблиц.

Импорт и примененные шаги

  1. Выбери источник

    Data › Get & Transform Data › Get Data › From File › From Text/CSV (другие варианты: From Workbook, From Folder, From Table/Range). Выбери файл и нажми Import.

  2. Открой редактор

    В окне предпросмотра нажми Transform Data. Откроется редактор Power Query: данные в центре, справа — список Query Settings › Applied Steps.

  3. Очисти

    Каждое действие (удаление пробелов, смена типа, фильтр строк) записывается в список как новый шаг. Щёлкни шаг — увидишь данные в тот момент; кнопкой × шаг удаляется.

  4. Загрузи на лист

    Home › Close & Load загружает результат на новый лист в виде таблицы Excel. Через Close & Load To… можно загрузить в сводную таблицу или только как подключение (Only Create Connection).

ПроблемаКоманда Power Query
3 пустые или служебные строки в начале файлаHome › Reduce Rows › Remove Rows › Remove Top Rows
Заголовки в первой строке данныхHome › Transform › Use First Row as Headers
Лишние пробелы, невидимые символыTransform › Text Column › Format › Trim / Clean
Разный регистр: «baku», «BAKU», «Baku»Format › Capitalize Each Word
Даты и суммы — текстTransform › Any Column › Data Type; right-click › Change Type › Using Locale…
«Hasanova Leyla» в одном столбцеTransform › Split Column › By Delimiter
Название группы только в первой строкеTransform › Fill › Down
Повторяющиеся строкиHome › Reduce Rows › Remove Rows › Remove Duplicates
Text
let
    Source = Csv.Document(File.Contents("C:\Data\baku.csv"), [Delimiter = ",", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Trimmed = Table.TransformColumns(Promoted, {{"Customer", Text.Trim, type text}}),
    Proper = Table.TransformColumns(Trimmed, {{"City", Text.Proper, type text}}),
    Typed = Table.TransformColumnTypes(Proper, {{"Date", type date}, {"Amount", type number}}),
    Filtered = Table.SelectRows(Typed, each [Amount] > 0)
in
    Filtered
Каждый шаг — строка на языке M: Home › Advanced Editor показывает весь запрос. Имя шага служит входом для следующего шага.

Интерактив

Загрузка симуляции…
Очистка «грязных» данных: формульный аналог шагов Power Query.

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

Append и Merge: объединение таблиц

КритерийAppend QueriesMerge Queries
Что делаетставит таблицы друг под другом (добавляет строки)соединяет таблицы бок о бок по ключевому столбцу (добавляет столбцы)
Условиестолбцы с одинаковыми именамиобщий ключ в обеих таблицах (например, ProductID)
Аналог в Excelскопировать и вставить нижеXLOOKUP
ПутьHome › Combine › Append QueriesHome › Combine › Merge Queries

Три филиала и каталог товаров

Задача

В файле Баку 1200 строк продаж, Гянджи — 800, Сумгайыта — 450 (столбцы одинаковые). Есть и каталог Products из 40 товаров: ProductID, Category, Price. Нужно видеть категорию каждой продажи.

Показать решениеСкрыть решение

Решение

1) Append Queries as New › Three or more tables › добавь три запроса → 2450 строк (1200 + 800 + 450).
2) Merge Queries as New: сверху результат Append, снизу Products; в обоих выдели ProductID; Join Kind: Left Outer (all from first, matching from second).
3) Нажми значок ⇆ в заголовке нового столбца и отметь только Category.
В результате по-прежнему 2450 строк, ведь каждой продаже соответствует ровно один товар.
Проверка: Join Kind › Left Anti покажет продажи с ProductID, которого нет в каталоге, — их должно быть 0.

Unpivot: столбцы в строки

Отчёты часто приходят «широкими»: Product | Jan | Feb | Mar. Человеку так удобно, но сводной таблице, фильтру и SUMIFS нужна «длинная» форма: Product | Month | Sales. Выдели столбец Product и выбери Transform › Any Column › Unpivot Columns › Unpivot Other Columns; затем переименуй Attribute в Month, а Value — в Sales. Вариант «Other Columns» удобен: если в следующем месяце появится столбец Apr, он развернётся автоматически.

ProductMonthSales
NotebookJan120
NotebookFeb135
NotebookMar150
PenJan300
PenFeb280
PenMar310
Результат Unpivot: из «широкой» таблицы 3 товара × 3 месяца получается «длинная» таблица из 9 строк (здесь первые 6).
Преобразовать диапазон в таблицу — подготовка к From Table/RangeCtrl+T
Обновить таблицу выбранного запросаAlt+F5
Обновить все запросы и подключенияCtrl+Alt+F5

Запомни

  • Power Query записывает шаги очистки; при изменении источника Refresh повторяет их все.
  • Основа очистки: Trim, Clean, регистр, правильные типы, Fill Down, Remove Duplicates.
  • Append добавляет строки (друг под другом), Merge — столбцы по ключу (бок о бок).
  • Power Query чувствителен к регистру и пробелам; перед Merge очищай ключи.
  • Unpivot Other Columns превращает широкую таблицу в длинную, готовую к анализу.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

Вопрос 1 / 10

Вопрос 1 / 10. Какая команда соберёт таблицы трёх филиалов с одинаковыми столбцами в одну, друг под другом?