Power Query для початківців: що це і як автоматизувати звіти в Excel і Power BI

Щопонеділка ви відкриваєте п'ять файлів від філій, копіюєте дані в один аркуш, видаляєте порожні рядки, виправляєте дати, підтягуєте назви товарів через ВПР — і так кожного тижня. Power Query робить усе це автоматично: ви один раз показуєте, що робити з даними, а далі натискаєте «Оновити».

У цій статті пояснимо простими словами, що таке Power Query, де його знайти в Excel і Power BI, як працює редактор, і розберемо на одному наскрізному прикладі п'ять задач, які він закриває в роботі аналітика. Після цього стане зрозуміло, чому Power Query — один із перших інструментів, які вчать майбутні аналітики даних.

Що таке Power Query

Power Query — це інструмент для отримання й підготовки даних, вбудований у Microsoft Excel і Power BI. У документації Microsoft його описано як рушій трансформації та підготовки даних (data transformation and data preparation engine). Простіше кажучи, Power Query бере дані звідки завгодно, приводить їх до ладу й передає в Excel-таблицю або модель даних.

Ключові можливості:

• Підключення до джерел: файли Excel, CSV і TXT, цілі папки з файлами, бази даних, вебсторінки, SharePoint, онлайн-сервіси.
• Перетворення: за даними Microsoft, понад 350 типів трансформацій — від видалення дублікатів до розгортання стовпців і групування.
• Повторюваність: усі дії записуються як кроки запиту. Коли з'являються нові дані, достатньо оновити запит.
• Безпечність: вихідні дані не змінюються, результат виводиться окремо.

По суті, Power Query — це ETL (extract, transform, load) для людей, які не пишуть код: отримати, перетворити, завантажити.

Де знайти Power Query в Excel і Power BI

Програма	Де шукати	Примітки
Excel для Windows (Microsoft 365, 2024, 2021, 2019, 2016)	Вкладка «Дані» (Data) → група отримання й перетворення даних → Get Data	Вбудований, окремо встановлювати не треба
Excel для Mac (Microsoft 365)	Вкладка «Дані» → Get Data	Оновлення підтримується для частини джерел: TXT, CSV, Excel, JSON, XML, SQL Server та деяких інших. Excel 2016 і 2019 для Mac Power Query не підтримують
Excel для вебу	Вкладка «Дані»	Доступна частина можливостей; Microsoft поступово їх розширює
Power BI Desktop	Home → Get Data, Transform Data	Повний редактор Power Query, від нього починається будь-який звіт
Excel 2010 і 2013	Окрема надбудова	Microsoft її більше не підтримує — варто оновити Excel

Назви команд у статті наведено за англомовним інтерфейсом. У локалізованих версіях Excel вони перекладені й можуть дещо відрізнятися залежно від версії, тому орієнтуйтеся на розташування кнопок.

Як працює редактор: запит і кроки

Коли ви підключаєтеся до джерела й натискаєте Transform Data, відкривається редактор Power Query. У ньому чотири важливі зони:

• Стрічка з командами — вкладки Home, Transform, Add Column з усіма перетвореннями.
• Список запитів ліворуч — кожне джерело або проміжна таблиця є окремим запитом.
• Попередній перегляд даних у центрі — як таблиця виглядає після поточного кроку.
• Applied Steps (застосовані кроки) праворуч — історія всіх дій.

Applied Steps — серце Power Query. Кожен клік («видалити стовпець», «змінити тип», «відфільтрувати») додає крок. На будь-який крок можна натиснути й побачити дані на тому етапі, видалити помилковий крок чи змінити його налаштування. Це схоже на запис макросу, але зрозумілий і редагований.

Задача 1. Об'єднати всі файли з папки

Наскрізний приклад: мережа магазинів, кожна з трьох філій щомісяця надсилає файл sales_kyiv.xlsx, sales_lviv.xlsx, sales_odesa.xlsx з однаковою структурою: товар (артикул) і продажі за місяцями в окремих стовпцях. Потрібен один звіт.

1. Покладіть усі файли в одну папку.
2. В Excel: Get Data → From File → From Folder, вкажіть папку.
3. У вікні зі списком файлів натисніть Combine → Combine & Transform Data.
4. Виберіть аркуш або таблицю-зразок. Power Query сам створить допоміжні запити й складе дані з усіх файлів в одну таблицю, додавши стовпець з назвою файлу.

Головна перевага: наступного місяця ви просто кладете нові файли в ту саму папку й оновлюєте запит. Копіювати нічого не потрібно.

Задача 2. Почистити дані

Реальні дані майже завжди «брудні». Типовий набір кроків очищення в редакторі:

Проблема	Команда	Де знайти
Порожні рядки внизу файлів	Remove Rows → Remove Blank Rows	Home
Пробіли на початку й у кінці тексту	Format → Trim	Transform
Дати й числа розпізнані як текст	Data Type (тип даних стовпця)	Home або Transform
Дублікати	Remove Rows → Remove Duplicates	Home
Зайві стовпці	Choose Columns / Remove Columns	Home
Різне написання значень («Київ», «м. Київ»)	Replace Values	Transform

Порада: задавайте типи даних свідомо й якомога пізніше, після основних перетворень. Тип «дата» чи «десяткове число» визначає, чи правильно потім рахуватимуться суми й фільтри за періодами.

Задача 3. Розгорнути місяці зі стовпців у рядки (Unpivot)

У файлах філій продажі розкладені по стовпцях: «Січень», «Лютий», «Березень»… Людині так зручно читати, але для аналізу й зведених таблиць потрібен «довгий» формат: один рядок — один товар за один місяць.

Було (широкий формат)	Стало (довгий формат)
Артикул | Січень | Лютий | Березень	Артикул | Місяць | Продажі
A-101 | 120 | 95 | 140	A-101 | Січень | 120 A-101 | Лютий | 95 A-101 | Березень | 140

Як зробити: виділіть стовпці, які мають залишитися (артикул, назва файлу), і виберіть Transform → Unpivot Columns → Unpivot Other Columns. Перейменуйте стовпці Attribute і Value на «Місяць» і «Продажі». Варіант «Unpivot Other Columns» кращий за звичайний: коли у файлах з'явиться новий місяць, він автоматично потрапить у результат.

Задача 4. Зіставити таблиці замість ВПР (Merge)

У продажах є лише артикул, а назва товару й категорія — в окремому довіднику. В Excel це зазвичай роблять функцією ВПР (VLOOKUP) або XLOOKUP. У Power Query для цього є Merge Queries:

1. Завантажте довідник товарів як окремий запит.
2. У запиті продажів виберіть Home → Merge Queries.
3. Вкажіть довідник і клацніть ключовий стовпець в обох таблицях — артикул.
4. Тип з'єднання — Left Outer: усі рядки продажів плюс відповідні рядки довідника.
5. Розгорніть новий стовпець і виберіть потрібні поля: назву й категорію.

Merge працює швидше за тисячі формул ВПР і не ламається, коли змінюється кількість рядків. Якщо ж треба не зіставити, а дописати одну таблицю під іншу (наприклад, продажі за 2025 і 2026 роки), використовуйте Append Queries. Типи з'єднань тут ті самі, що й у SQL: якщо ви знайомі з JOIN зі статті «SQL для початківців», логіка буде знайома.

Задача 5. Завантажити результат і оновлювати одним кліком

1. У редакторі натисніть Home → Close & Load (або Close & Load To, щоб вибрати: таблиця на аркуші, лише підключення чи модель даних).
2. На основі таблиці побудуйте зведену таблицю або діаграму.
3. Коли з'являться нові файли, натисніть Data → Refresh All — Power Query повторить усі кроки.

Для великих обсягів даних завантажуйте результат у модель даних, а не на аркуш: модель тримає мільйони рядків і дає змогу будувати зв'язки між таблицями й обчислення на DAX.

Мова M: що варто знати новачку

Кожен крок, який ви робите мишкою, Power Query записує мовою M (офіційна назва — Power Query Formula Language). Побачити весь код запиту можна через Home → Advanced Editor. Ось як виглядає спрощений запит із задачі 3:

let
    Source = Excel.Workbook(File.Contents("C:\Reports\sales_kyiv.xlsx"), true),
    Sales = Source{[Item = "Sales", Kind = "Sheet"]}[Data],
    Unpivoted = Table.UnpivotOtherColumns(Sales, {"Артикул"}, "Місяць", "Продажі"),
    Typed = Table.TransformColumnTypes(Unpivoted, {{"Продажі", type number}})
in
    Typed

Читати M просто: у блоці let кожен рядок — це крок з назвою, і кожен наступний крок бере результат попереднього. Те, що стоїть після in, — результат запиту. Новачкові не треба писати M з нуля: достатньо розуміти структуру, щоб підправити шлях до файлу, назву стовпця чи умову фільтра.

Окремо про AI: Microsoft розгортає в Excel для вебу функцію Copilot, яка за текстовим описом даних сама будує запит Power Query, який потім можна редагувати й оновлювати (за дорожньою картою Microsoft 365, жовтень 2026 року). Це пришвидшує старт, але перевіряти кроки все одно доведеться вам — тому розуміння редактора й M нікуди не зникає.

Power Query, Power Pivot і Power BI: хто за що відповідає

Інструмент	Відповідає за	Де живе
Power Query	Отримати, почистити й об'єднати дані	Excel, Power BI, онлайн-сервіси Microsoft
Power Pivot (модель даних)	Зв'язати таблиці, зберігати великі обсяги	Excel; у Power BI — модель звіту
DAX	Обчислення й показники на моделі	Power Pivot, Power BI
Power BI	Інтерактивні звіти й дашборди, публікація для команди	Power BI Desktop і сервіс

Якщо вагаєтеся, чи вам вистачить Excel, чи пора переходити на Power BI, — порівняння є в статті «Power BI vs Excel: що краще обрати». Приємна новина: Power Query в обох програмах однаковий, тож навички переносяться без змін.

Типові помилки

• Абсолютний шлях до файлу на своєму комп'ютері. Колега відкриває звіт — і запит не знаходить файлів. Зберігайте джерела в спільній папці або на SharePoint.
• Зміна типу даних на першому ж кроці. Power Query часто додає крок Changed Type автоматично; якщо згодом з'явиться новий стовпець, цей крок може зламатися. Перевіряйте і, за потреби, переносьте його нижче.
• Unpivot Columns замість Unpivot Other Columns. Новий місяць у даних не потрапить у звіт.
• Inner join замість Left Outer. Рядки продажів без відповідного артикула в довіднику тихо зникають.
• Усе на аркуші. Сотні тисяч рядків на аркуші гальмують Excel; завантажуйте в модель даних.

Часті запитання

Що таке Power Query простими словами?

Power Query — це інструмент для отримання й підготовки даних, вбудований у Excel і Power BI. Він підключається до файлів, папок, баз даних чи вебсторінок, чистить і перетворює дані за записаними кроками, а потім оновлює результат одним кліком. Ви налаштовуєте обробку один раз, і далі вона повторюється автоматично.

Де знайти Power Query в Excel?

У Excel для Microsoft 365, 2024, 2021, 2019 і 2016 для Windows Power Query вбудований: це група команд на вкладці «Дані» (Data) для отримання й перетворення даних, а кнопка Get Data відкриває список джерел. Для Excel 2010 і 2013 існувала окрема надбудова, але Microsoft її більше не підтримує.

Чи змінює Power Query вихідний файл?

Ні. За документацією Microsoft, Power Query не змінює вихідні дані: він читає їх, застосовує ваші кроки й виводить результат в окрему таблицю або модель даних. Тому можна сміливо експериментувати — оригінальний файл залишиться таким, як був.

Чи працює Power Query на Mac і в Excel для вебу?

Так, але з обмеженнями. Excel для Mac у Microsoft 365 підтримує Power Query і оновлення з частини джерел — текстових файлів, CSV, Excel, JSON, XML, SQL Server та деяких інших. Excel 2016 і 2019 для Mac його не підтримують. В Excel для вебу доступна частина можливостей, і Microsoft поступово їх розширює.

Чи потрібно вчити мову M?

Для старту — ні. Кожна дія в редакторі автоматично записується як рядок коду мовою M, і більшість задач розв'язується кнопками. Базове читання M знадобиться, коли захочете підправити крок, який неможливо налаштувати мишкою, або зробити запит гнучкішим. Почніть із того, щоб відкривати Advanced Editor і дивитися, який код створюють ваші дії.

Чим Power Query відрізняється від Power Pivot і DAX?

Power Query відповідає за підготовку даних: отримати, почистити, об'єднати. Power Pivot — це модель даних у Excel, де таблиці пов'язуються між собою, а DAX — мова формул для обчислень у цій моделі й у Power BI. Типовий порядок роботи такий: Power Query готує таблиці, модель їх пов'язує, DAX рахує показники.

Скільки часу потрібно, щоб вивчити Power Query?

Базові операції — імпорт, очищення, об'єднання таблиць і оновлення — можна освоїти за 1–2 тижні регулярної практики на власних даних. Впевнена робота з мовою M і складними сценаріями приходить за кілька місяців. Найшвидше вчаться ті, хто одразу переносить на Power Query свою реальну рутину зі звітами.

Джерела

• Microsoft Learn: What is Power Query?
• Microsoft Support: Про Power Query у програмі Excel
• Microsoft Learn: Power Query M formula language
• Microsoft Learn: Unpivot columns
• Microsoft Learn: Merge queries overview

Вивчити Power BI і Power Query →