В начале каждого месяца бухгалтер получает CSV-файлы от трёх филиалов. В именах лишние пробелы, «BAKU», «baku» и «Baku» вперемешку, даты приходят текстом, а продажи по месяцам разбросаны по столбцам. Такая чистка занимает два часа каждый месяц. Power Query записывает эти шаги один раз, а в следующем месяце достаточно нажать Refresh.
Power Query — встроенный в Excel начиная с версии 2016 инструмент «получить, преобразовать, загрузить»; тот же движок работает в Power BI. Он никогда не меняет исходный файл: все преобразования хранятся внутри запроса. Простое правило: подготовка данных (импорт, очистка, объединение) — работа Power Query, а расчёты — работа формул и сводных таблиц.
Импорт и примененные шаги
Выбери источник
Data › Get & Transform Data › Get Data › From File › From Text/CSV(другие варианты:From Workbook,From Folder,From Table/Range). Выбери файл и нажмиImport.Открой редактор
В окне предпросмотра нажми
Transform Data. Откроется редактор Power Query: данные в центре, справа — списокQuery Settings › Applied Steps.Очисти
Каждое действие (удаление пробелов, смена типа, фильтр строк) записывается в список как новый шаг. Щёлкни шаг — увидишь данные в тот момент; кнопкой
×шаг удаляется.Загрузи на лист
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 |
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
FilteredHome › Advanced Editor показывает весь запрос. Имя шага служит входом для следующего шага.Интерактив
Чтобы читать дальше, зарегистрируйся бесплатно
Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.
Append и Merge: объединение таблиц
| Критерий | Append Queries | Merge Queries |
|---|---|---|
| Что делает | ставит таблицы друг под другом (добавляет строки) | соединяет таблицы бок о бок по ключевому столбцу (добавляет столбцы) |
| Условие | столбцы с одинаковыми именами | общий ключ в обеих таблицах (например, ProductID) |
| Аналог в Excel | скопировать и вставить ниже | XLOOKUP |
| Путь | Home › Combine › Append Queries | Home › Combine › Merge Queries |
Три филиала и каталог товаров
Задача
В файле Баку 1200 строк продаж, Гянджи — 800, Сумгайыта — 450 (столбцы одинаковые). Есть и каталог Products из 40 товаров: ProductID, Category, Price. Нужно видеть категорию каждой продажи.
Показать решениеСкрыть решение
Решение
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, он развернётся автоматически.
| Product | Month | Sales |
|---|---|---|
| Notebook | Jan | 120 |
| Notebook | Feb | 135 |
| Notebook | Mar | 150 |
| Pen | Jan | 300 |
| Pen | Feb | 280 |
| Pen | Mar | 310 |
From Table/RangeCtrl+TЗапомни
- Power Query записывает шаги очистки; при изменении источника
Refreshповторяет их все. - Основа очистки:
Trim,Clean, регистр, правильные типы,Fill Down,Remove Duplicates. - Append добавляет строки (друг под другом), Merge — столбцы по ключу (бок о бок).
- Power Query чувствителен к регистру и пробелам; перед Merge очищай ключи.
Unpivot Other Columnsпревращает широкую таблицу в длинную, готовую к анализу.