Excel Power Query у 2026: ETL для аналітиків даних та питання на співбесіді

Excel Power Query трансформує спосіб, яким аналітики даних реалізують ETL-процеси (Extract, Transform, Load) безпосередньо в Excel. На відміну від ручного копіювання або складних макросів VBA, Power Query пропонує візуальний інтерфейс, підтримуваний мовою M, що дозволяє створювати повторювані трансформації даних, які оновлюються одним кліком.

Які проблеми вирішує Power Query для аналітиків даних

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

Робочий процес складається з трьох етапів: підключення до джерел даних, застосування трансформацій та завантаження результатів у таблиці Excel або модель даних. Кожен крок записується у рядку формул з використанням синтаксису мови M, який можна редагувати безпосередньо для складних сценаріїв.

Цей підхід відрізняється від традиційних формул Excel. Поки формули перераховують комірки, Power Query працює з цілими таблицями до того, як вони потрапляють на аркуш. Запит, який консолідує 50 CSV-файлів, видаляє дублікати та розвертає стовпці, виконується один раз і створює чисту таблицю замість побудови складних вкладених формул, що сповільнюють книгу.

Підключення до джерел даних через Отримати дані

Power Query підтримує підключення до файлів (CSV, Excel, JSON, XML), баз даних (SQL Server, MySQL, PostgreSQL, Oracle), хмарних служб (SharePoint, Azure, Salesforce) та веб-сторінок. Тип підключення визначає, які параметри автентифікації та імпорту відображатимуться.

// Типові джерела даних у Power Query
Дані > Отримати дані > З файлу > З файлу CSV
Дані > Отримати дані > З бази даних > З бази даних SQL Server
Дані > Отримати дані > З інших джерел > З Інтернету
Дані > Отримати дані > З папки (кілька файлів)

Опція "З папки" особливо корисна для консолідації кількох файлів. Замість імпорту кожного файлу окремо, Power Query сканує папку, виводить список усіх відповідних файлів та об'єднує їх в один запит. Додавання нового файлу до папки автоматично включає його при наступному оновленні.

При підключенні до бази даних SQL, Power Query може передати логіку трансформації на сервер через згортання запитів (query folding). Фільтри та вибір стовпців перетворюються на SQL-речення WHERE та SELECT, зменшуючи обсяг даних, що передаються до Excel. Рядок формул показує опцію "Переглянути власний запит", коли згортання активне.

Базові трансформації в Редакторі запитів

Редактор запитів надає команди стрічки для типових операцій, але розуміння базового коду M допомагає, коли потрібні налаштування. Кожна трансформація додає крок до панелі "Застосовані кроки", створюючи послідовність для аудиту.

Фільтрування та сортування рядків

Фільтрування видаляє рядки, що не відповідають критеріям. Випадаюче меню заголовка стовпця надає швидкі фільтри, тоді як діалогове вікно "Фільтрувати рядки" підтримує складні умови з логікою AND/OR.

// Крок FilteredRows мовою M
let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Region] = "EMEA" and [Amount] > 1000)
in
    FilteredRows

Функція Table.SelectRows приймає таблицю та умову. Ключове слово each створює функцію, де _ представляє поточний рядок, а доступ до полів використовує квадратні дужки [Region]. Кілька умов об'єднуються операторами and або or.

Видалення та перейменування стовпців

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

// Видалити стовпці, потім перейменувати решту
let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    RemovedColumns = Table.RemoveColumns(Source, {"TempID", "InternalNotes", "Debug"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns, {{"Cust_Name", "CustomerName"}, {"Amt", "Amount"}})
in
    RenamedColumns

Список стовпців використовує фігурні дужки {} для кількох елементів. Перейменування приймає список пар, де кожна пара містить стару та нову назви. Послідовні конвенції іменування в запитах полегшують об'єднання наборів даних.

Розділення та об'єднання стовпців

Текстові стовпці часто потребують розбору. Стовпець "FullName" може потребувати розділення на ім'я та прізвище, або окремі стовпці дати та часу можуть потребувати об'єднання.

// Розділити FullName за роздільником на два стовпці
let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    SplitColumn = Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"FirstName", "LastName"})
in
    SplitColumn

Функція Splitter.SplitTextByDelimiter обробляє логіку розбору. Для складніших шаблонів Splitter.SplitTextByEachDelimiter або Splitter.SplitTextByPositions пропонують додатковий контроль. Коли кількість результуючих стовпців змінюється, Power Query створює стовпці динамічно.

Перетворення типів та якість даних

Power Query визначає типи стовпців при імпорті, але явне призначення типів дозволяє виявити помилки завчасно. Текстовий стовпець з числовими ID повинен залишатися текстом, якщо провідні нулі мають значення. Стовпці дат, імпортовані як текст, спричиняють проблеми з сортуванням.

// Явні призначення типів
let
    Source = Csv.Document(File.Contents("C:\\Data\\transactions.csv")),
    TypedColumns = Table.TransformColumnTypes(Source, {
        {"TransactionID", type text},
        {"Date", type date},
        {"Amount", type number},
        {"IsProcessed", type logical}
    })
in
    TypedColumns

Ключове слово type вказує цільовий тип. Доступні типи включають text, number, date, datetime, datetimezone, time, duration, logical та binary. Помилки типів відображаються як значення "Error" у комірках, роблячи проблеми якості даних видимими до аналізу.

Обробка значень null вимагає явної логіки. Функція Table.ReplaceValue замінює null на значення за замовчуванням, тоді як Table.SelectRows з [Column] <> null фільтрує їх.

Групування та агрегація за допомогою Групувати за

Агрегування даних за категоріями є частою вимогою. Трансформація "Групувати за" згортає рядки з однаковими ключовими значеннями та застосовує агрегатні функції.

// Групувати продажі за регіоном та роком, обчислити суму та кількість
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source, {"Region", "Year"}, {
        {"TotalSales", each List.Sum([Amount]), type number},
        {"OrderCount", each Table.RowCount(_), Int64.Type},
        {"AvgOrderValue", each List.Average([Amount]), type number}
    })
in
    Grouped

Стовпці групування з'являються першими, потім визначення агрегації. Кожна агрегація вказує назву нового стовпця, агрегатну функцію та необов'язковий тип результату. Ключове слово each представляє підтаблицю для кожної групи, дозволяючи використовувати будь-яку табличну або спискову функцію.

Вкладені агрегації дозволяють обчислення на кшталт "відсоток від суми групи" через посилання на значення рядка та агрегат групи в наступному кроці.

Зведення та розгортання для перетворення даних

Зведення (pivot) перетворює значення рядків на стовпці, створюючи перехресний макет. Розгортання (unpivot) робить зворотне, перетворюючи стовпці на рядки для нормалізованих структур.

// Розгорнути місячні стовпці в рядки
let
    Source = Excel.CurrentWorkbook(){[Name="MonthlySales"]}[Content],
    // Оригінал: стовпці Product, Jan, Feb, Mar, Apr
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")
    // Результат: стовпці Product, Month, Sales
in
    Unpivoted

Функція Table.UnpivotOtherColumns зберігає вказані стовпці фіксованими та розгортає решту. Це безпечніше, ніж перераховувати всі стовпці для розгортання, оскільки додавання нових місячних стовпців автоматично їх включить. Два останні параметри називають стовпець атрибута ("Month") та стовпець значення ("Sales").

Зведення використовує Table.Pivot з агрегатною функцією для випадків, коли існує кілька значень для однієї комбінації рядок-стовпець:

// Звести продажі за регіоном
let
    Source = Excel.CurrentWorkbook(){[Name="DetailedSales"]}[Content],
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", "Amount", List.Sum)
in
    Pivoted

Злиття та додавання запитів

Об'єднання даних з кількох джерел - це те, де Power Query зменшує ручну роботу. Злиття виконує з'єднання між двома таблицями на основі відповідних стовпців. Додавання складає таблиці вертикально.

// Left join: Замовлення з деталями клієнта
let
    Orders = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Customers = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    Merged = Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"ID"}, "CustomerDetails", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "CustomerDetails", {"Name", "Email"})
in
    Expanded

Функція Table.NestedJoin створює вкладений стовпець таблиці, що містить відповідні рядки. Функція Table.ExpandTableColumn потім розгортає вкладену структуру у звичайні стовпці. Типи з'єднань включають Inner, LeftOuter, RightOuter, FullOuter, LeftAnti та RightAnti.

Додавання за допомогою Table.Combine вимагає відповідних назв стовпців. Коли схеми відрізняються, Table.SelectColumns на кожному джерелі перед об'єднанням забезпечує узгодженість:

// Додати дві таблиці продажів з узгодженими стовпцями
let
    Sales2024 = Table.SelectColumns(Source2024, {"Date", "Product", "Amount"}),
    Sales2025 = Table.SelectColumns(Source2025, {"Date", "Product", "Amount"}),
    Combined = Table.Combine({Sales2024, Sales2025})
in
    Combined

Користувацькі стовпці та умовна логіка

Функція "Додати стовпець > Користувацький стовпець" дозволяє обчислювані поля з використанням виразів M. Умовна логіка використовує синтаксис if-then-else.

// Додати обчислюваний стовпець з умовною логікою
let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    AddedColumn = Table.AddColumn(Source, "OrderCategory", each 
        if [Amount] >= 10000 then "Enterprise"
        else if [Amount] >= 1000 then "Business"
        else "Consumer",
        type text
    )
in
    AddedColumn

Вираз if повинен містити обидві гілки then та else. Вкладені умови з'єднуються через else if. Останній параметр вказує тип стовпця, покращуючи продуктивність та запобігаючи проблемам з виведенням типів.

Для складних трансформацій допоміжні функції, визначені в блоці let, зберігають читабельність коду:

let
    // Допоміжна функція для фіскального кварталу
    GetFiscalQuarter = (date as date) as text =>
        let
            month = Date.Month(date),
            fiscalQ = if month >= 4 and month <= 6 then "Q1"
                     else if month >= 7 and month <= 9 then "Q2"
                     else if month >= 10 and month <= 12 then "Q3"
                     else "Q4"
        in
            fiscalQ,
    
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    AddedQuarter = Table.AddColumn(Source, "FiscalQuarter", each GetFiscalQuarter([Date]), type text)
in
    AddedQuarter

Обробка помилок у Power Query

Помилки трансформації відображаються як значення "Error" у комірках, а не спричиняють збій усього запиту. Конструкція try-otherwise обробляє помилки елегантно:

// Обробити потенційні помилки ділення
let
    Source = Excel.CurrentWorkbook(){[Name="Metrics"]}[Content],
    AddedRatio = Table.AddColumn(Source, "Ratio", each 
        try [Value1] / [Value2] otherwise null,
        type number
    )
in
    AddedRatio

Ключове слово try намагається виконати вираз і повертає запис з полями HasError та Value. Речення otherwise надає резервне значення, коли HasError є true. Для більшого контролю використовуйте try Expression для доступу до деталей помилки:

// Захопити деталі помилки
let
    result = try SomeRiskyFunction(),
    output = if result[HasError] then "Error: " & result[Error][Message] else result[Value]
in
    output

Питання на співбесіді з Power Query для аналітиків даних

Інтерв'юери оцінюють як практичні навички, так і розуміння того, коли Power Query підходить для робочого процесу. Ці питання часто з'являються на співбесідах для аналітиків даних.

Що таке згортання запитів (query folding) і чому це важливо?

Згортання запитів перетворює трансформації Power Query на власні запити джерела даних. При підключенні до SQL Server крок фільтрації стає реченням WHERE, що виконується на сервері, зменшуючи мережевий трафік. Не всі трансформації згортаються: користувацькі функції M, певні маніпуляції з датами та операції після кроку, що не згортається, розривають ланцюжок. Перевірте статус згортання, клацнувши правою кнопкою на кроці та шукаючи "Переглянути власний запит".

Чим Power Query відрізняється від формул Excel для трансформації даних?

Формули Excel працюють комірка за коміркою на аркуші та перераховуються при кожній зміні. Power Query працює з таблицями до того, як вони потрапляють на аркуш, обробляючи дані масово під час оновлення. Для трансформацій на кшталт розгортання, дедуплікації або злиття файлів Power Query виражає логіку більш прямо, ніж вкладені INDEX-MATCH або допоміжні стовпці.

Коли ви б обрали Power Query замість Power BI для ETL?

Power Query в Excel підходить для сценаріїв, коли аналітикам потрібні трансформовані дані у формі електронної таблиці для ad-hoc аналізу, зведених таблиць або обміну з користувачами без доступу до Power BI. Power BI надає багатшу візуалізацію, більшу ємність даних та корпоративні функції обміну. Той самий код M працює в обох інструментах, тому запити, розроблені в Excel, можуть мігрувати до Power BI Desktop без переписування.

Як ви обробляєте невідповідності типів даних при додаванні таблиць?

Встановіть явні типи на кожній вихідній таблиці перед об'єднанням за допомогою Table.Combine. Якщо стовпець є текстом в одному джерелі та числом в іншому, додавання не вдасться або згенерує помилки. Використовуйте Table.TransformColumnTypes на обох джерелах, щоб забезпечити узгоджені типи. Патерн try-otherwise обробляє граничні випадки, коли перетворення не вдається для конкретних значень.

Поясніть різницю між Злиттям та Додаванням у Power Query.

Злиття виконує горизонтальне з'єднання на основі відповідних ключових стовпців, подібно до SQL JOIN. Додавання виконує вертикальне об'єднання рядків з кількох таблиць, подібно до SQL UNION ALL. Злиття вимагає принаймні одного спільного стовпця для зіставлення. Додавання вимагає стовпців з однаковими назвами для правильного вирівнювання.

Для глибшої підготовки з концепцій SQL, які доповнюють навички Power Query, модуль віконних функцій SQL охоплює патерни ранжування та агрегації, тоді як модуль підзапитів та CTE SQL розглядає техніки структурування запитів.

Опції завантаження та стратегії оновлення

Після трансформацій кнопка "Закрити та завантажити" пропонує варіанти завантаження: завантажити в таблицю аркуша, завантажити тільки в модель даних (Power Pivot) або створити запит лише для підключення. Запити лише для підключення служать проміжними кроками для інших запитів без займання місця на аркуші.

Поведінка оновлення залежить від місця призначення завантаження. Таблиці аркуша оновлюються кнопкою "Оновити все" або можуть бути налаштовані на оновлення при відкритті файлу. Таблиці моделі даних беруть участь у циклі оновлення моделі даних книги. Для запитів, підключених до зовнішніх баз даних, можуть з'явитися запити облікових даних при оновленні, якщо збережені облікові дані відсутні.

Фонове оновлення дозволяє книзі залишатися придатною для використання під час завантаження даних, але створює складність, коли залежні формули покладаються на оновлені дані. Опція "Увімкнути фонове оновлення" у властивостях запиту контролює цю поведінку. Для критичних звітів вимкнення фонового оновлення забезпечує послідовне виконання.

Міркування щодо продуктивності для великих наборів даних

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

Для запитів, що займають кілька хвилин, "Попередній перегляд даних" у Редакторі запитів показує лише вибірку. Трансформації застосовуються до повного набору даних під час оновлення. Помилки, видимі в попередньому перегляді, вказують на проблеми, але деякі помилки з'являються лише з повними даними. Запуск тестового оновлення на підмножині перевіряє запит перед обробкою всього.

При консолідації багатьох файлів функція бінарного об'єднання Power Query обробляє файли паралельно. Визначення функції, що трансформує один файл, а потім виклик її для кожного рядка в списку файлів забезпечує більший контроль, ніж підхід об'єднання за замовчуванням.

Документація Microsoft щодо найкращих практик Power Query детально описує техніки оптимізації, включаючи структури залежностей запитів та використання буферів.

Power Query для повторюваних ETL-конвеєрів

Записані кроки в Power Query створюють документацію логіки трансформації. Коли вимоги змінюються, модифікація кроку автоматично оновлює всі наступні кроки. Це контрастує з ad-hoc маніпуляціями Excel, які вимагають відтворення з нуля, коли вихідні дані змінюються.

Команди можуть ділитися запитами, експортуючи підключення або зберігаючи код M запиту в системі контролю версій. Розширений редактор (Перегляд > Розширений редактор) відображає повний скрипт M, який можна скопіювати та вставити в іншу книгу. Для корпоративних сценаріїв потоки даних Power BI та Fabric забезпечують централізоване управління запитами.

• Power Query обробляє ETL в Excel, використовуючи записані, повторювані кроки трансформації
• Мова M, що лежить в основі візуального інтерфейсу, дозволяє налаштування за межами команд стрічки
• Згортання запитів передає фільтри та проєкції до вихідних баз даних для кращої продуктивності
• Злиття виконує з'єднання між таблицями, Додавання складає таблиці вертикально
• Призначення типів та обробка помилок запобігають прихованим проблемам якості даних
• Запити лише для підключення створюють багаторазові проміжні кроки без виводу на аркуш
• Питання на співбесіді зосереджуються на згортанні запитів, порівнянні з формулами та відмінностях між злиттям і додаванням