Як використовувати Power Query в Excel: повний посібник

Power Query — це потужний інструмент бізнес-аналітики в Excel, який дає змогу імпортувати, очищати та перетворювати дані відповідно до ваших потреб. Якщо ви коли-небудь проводили години, очищаючи дані (у нас є) або намагаючись об’єднати дані з різних джерел (у нас також є), ця функція полегшить вам життя.

Найкращий спосіб навчитися використовувати Power Query в Excel — це виконати деякі звичайні завдання, наприклад імпортувати дані та використовувати редактор Power Query для перетворення отриманих таблиць і об’єднання даних.

Ця стаття допоможе вам почати, показуючи чіткі . Освоївши Power Query, ви зможете підняти свою роботу на наступний рівень.

Ви готові? Давай зробимо це.

Зміст

• Як дізнатися, чи можна отримати доступ до Power Query
• Як імпортувати дані за допомогою Power Query
• Як користуватися редактором Power Query
  • Попередній перегляд даних
  • Властивості
• Як працювати з кількома джерелами даних
• Як додати дані за допомогою Power Query
• Як об’єднати дані з Power Query
• 5 способів перетворення даних за допомогою Power Query
  • 1. Розгортання та згортання даних
  • 2. Сортування даних
  • 3. Форматування даних
  • 4. Додавання та видалення стовпців
  • 5. Зміна типів даних
• Робота з формулами та мовою M
  • Панель формул
  • Написання та зміна коду M
• Що таке синтаксис мови M?
• Як оновити підключення даних
• Як інтегрувати Power Query із моделлю даних
• Як автоматизувати за допомогою Power Query і VBA
• Розширені функції Power Query
• Співпраця та обмін
• Давайте завершимо це

Як дізнатися, чи можна отримати доступ до Power Query

Перш ніж почати використовувати Power Query, потрібно переконатися, що у вас є версія Excel, яка має цю функцію.

Ось короткий огляд Power Query за версіями:

• Excel 2010 і Excel 2013: доступні як надбудова, яку потрібно завантажити та встановити.

• Excel 2016: інтегровано в Excel як «Отримати та трансформувати».

• Excel 2019: покращена продуктивність і функціональність.

• Excel 2021 і Excel для Microsoft 365: інтеграція з хмарними джерелами даних.

• Excel для Mac: доступно лише з Microsoft 365 версії 16.69 і пізніших.

Щоб переконатися, що інструмент інтегровано у вашу робочу книгу Excel (2016 або пізнішої версії), перейдіть на вкладку «Дані» та знайдіть на цьому зображенні розділ «Отримати та перетворити дані»:

Якщо у вас є Excel 2010 або 2013 у Windows, ви можете завантажити , інсталювати та ввімкнути надбудову Power Query.

Як імпортувати дані за допомогою Power Query

Інструмент Power Query дозволяє імпортувати та підключатися до широкого діапазону джерел даних:

• Файли, включаючи формати CSV, XML і JSON

• Веб-URL-адреси або кінцеві точки API

• Бази даних, включаючи Access, SQL Server, Oracle і більшість сучасних баз даних

• Служби Azure, файли SharePoint і канали OData

Найпростішим джерелом даних для початку є простий файл CSV із кількома рядками даних. Створіть текстовий файл і додайте ці чотири імена та прізвища, розділені комою:

• Джо, Bloggs

• Енн, Раян

• Джеймс, Стюарт

• Мері, Браун

Обов’язково збережіть файл із розширенням .CSV. Ваш файл виглядатиме так:

Щоб завантажити дані за допомогою Power Query, виконайте такі дії:

1. Перейдіть на вкладку Дані.

2. Натисніть кнопку «Отримати файли» на вкладці «Отримати та перетворити дані».

3. Виберіть файл зразка.

Можливо, вам доведеться зачекати кілька секунд, поки інструмент завантажить дані на панель попереднього перегляду. Power Query використовує досить багато машинних ресурсів навіть для невеликого імпорту, тому будьте терплячі.

Порада: закрийте інші програми, щоб прискорити роботу.

Power Query відобразить вікно «Імпорт тексту/CSV» із попереднім переглядом ваших даних.

Якщо дані виглядають правильно, ви можете продовжити завантаження або перетворення даних:

• кнопка Завантажити завантажить дані безпосередньо в Excel.

• кнопка Transform Data дозволяє змінювати дані перед їх завантаженням.

Ви помітили, що файл зразка не мав заголовка? Якщо його завантажувати безпосередньо в Excel, заголовками будуть «Стовпець 1» і «Стовпець 2».

Натомість ви можете перетягнути дані в редактор Power Query і внести деякі зміни. Для цього натисніть кнопку «Перетворити дані».

Відкриється редактор Power Query, який переведе нас до наступного розділу.

Як користуватися редактором Power Query

У цьому розділі ми розглянемо деякі основні аспекти роботи з редактором Power Query.

Попередній перегляд даних

В області попереднього перегляду даних ви можете переглядати імпортовані дані та взаємодіяти з ними. Ви можете фільтрувати рядки, змінювати типи даних стовпців і змінювати форму даних відповідно до своїх потреб.

Коли ви закінчите, Excel завантажить перетворені дані на аркуш.

Використовуючи файл зразка, ви можете редагувати назви стовпців безпосередньо на цій панелі.

Робити так:

1. Клацніть правою кнопкою миші заголовок стовпця.

2. У спадному меню натисніть «Перейменувати».

3. Введіть бажані імена в поля заголовка.

Коли ви вносите зміни, розділ «Застосовані кроки» праворуч від панелі попереднього перегляду оновлюється, щоб відобразити кожне перетворення, що дозволяє відстежувати та змінювати кроки обробки даних за потреби.

Властивості

Налаштування властивостей для ваших запитів є важливим для організації та відстеження вашої роботи.

Панель параметрів запиту знаходиться праворуч від вікна Power Query.

1. Натисніть посилання «Усі властивості».

2. Відредагуйте назву запиту, щоб вона була описовою.

3. Додайте опис, щоб надати більше інформації.

Легко забути, для чого були налаштовані запити минулого тижня. У цьому випадку ім’я запиту було змінено та додано опис, який визначає мету запиту.

Якщо ви хочете дізнатися більше про доступні варіанти, перегляньте це.

Як працювати з кількома джерелами даних

Редактор Power Query пропонує два основні методи об’єднання даних із кількох джерел: додавання та об’єднання.

Додавання з’єднує таблиці вертикально, накладаючи їх одна на одну. Об’єднання дозволяє об’єднувати таблиці на основі спільних стовпців.

Щоб побачити, як це працює, створіть другий невеликий файл CSV із такою ж структурою, що й перший. Цього разу введіть у файл два імені (ім’я та прізвище):

• Джо, Bloggs

• Лаура, Кейн

Зверніть увагу, що одне з цих імен повторюється з попереднього файлу.

Тепер виконайте ці кроки, щоб перенести дані у свою книгу Microsoft Excel.

1. Закрийте вікно редактора Power Query і збережіть зміни.

2. Додати новий аркуш.

3. Виконайте кроки з попереднього розділу, щоб імпортувати другий файл.

4. Натисніть кнопку «Трансформувати».

5. Перейменуйте заголовки стовпців на «Ім’я» та «Прізвище».

Права панель аркуша Excel відображає поточні запити та підключення. Тепер ви повинні побачити два запити на панелі.

Як додати дані за допомогою Power Query

У цьому розділі ви додасте другий зразок файлу CSV до першого.

Щоб додати дані, виконайте наведені нижче дії.

1. Клацніть правою кнопкою миші першу таблицю (Імена клієнтів) на панелі «Запити та підключення».

2. Виберіть «Редагувати», щоб відкрити таблицю в редакторі Power Query.

3. Натисніть «Додати запити» в розділі «Об’єднати».

4. Виберіть другу таблицю зі спадного списку таблиць, які потрібно додати.

Результатом буде два додаткових рядки в першій таблиці.

Зверніть увагу, що один рядок повторюється – так працює операція додавання. Він додає дані з однієї таблиці в іншу, незалежно від того, чи дані вже існують.

Ви можете видалити повторювані дані в редакторі Power Query, виконавши такі дії:

1. Натисніть на заголовок стовпця «Ім’я».

2. Утримуйте клавішу «Ctrl» (або клавішу «Cmd» на Mac).

3. Клацніть заголовки обох стовпців.

4. Натисніть «Видалити рядки» на вкладці «Домашня сторінка» стрічки редактора Power Query.

5. У спадному меню виберіть «Видалити дублікати».

Як об’єднати дані з Power Query

Коли ви об’єднуєте дані, ви повинні вибрати відповідні стовпці між запитами.

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

Дотримуйтесь інструкцій у попередніх розділах, щоб завантажити кожен файл у власний запит потужності. Потім можна попрактикуватися в об’єднанні таблиць.

У випадку наших вибіркових даних обидва стовпці (Ім’я та Прізвище) слід вибрати як відповідні стовпці. Утримуйте клавішу Ctrl , щоб вибрати кілька стовпців.

Виконайте такі дії:

1. Клацніть правою кнопкою миші першу таблицю на панелі «Запити та підключення».

2. Виберіть «Редагувати», щоб відкрити таблицю в редакторі Power Query.

3. Виберіть «Об’єднати запити» з групи «Об’єднати».

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

5. Натисніть на перший стовпець у першій таблиці.

6. Утримуючи клавішу Ctrl, клацніть другий стовпець у першій таблиці.

7. Утримуючи клавішу Ctrl, клацніть обидва стовпці другої таблиці.

8. Збережіть оператор з’єднання за замовчуванням як «Лівий зовнішній».

9. Натисніть «ОК», щоб застосувати зміни.

Оператор об’єднання «Лівий зовнішній» забезпечує усунення дублікатів даних.

У редакторі Power Query ви побачите новий стовпець під назвою «Таблиця» або «Вкладена таблиця», який містить об’єднані дані з другого запиту.

1. Натисніть значок розгортання (дві стрілки) у заголовку цього стовпця.

2. Зніміть прапорець «використовувати оригінальний стовпець як префікс» (це дозволить уникнути довгих імен стовпців).

3. Натисніть «ОК».

4. Натисніть «Закрити та завантажити».

Об’єднана таблиця мала чотири стовпці з об’єднаними телефоном і адресою. Можливо, вам ще доведеться трохи навести порядок.

Наприклад, ви можете змінити назву таблиці (Merge1) і стовпець адреси на зображенні нижче.

5 способів перетворення даних за допомогою Power Query

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

Деякі з найпоширеніших способів перетворення даних:

• Розгортання та згортання даних

• Сортування даних

• Форматування даних

• Додавання та видалення стовпців

• Зміна типів даних

1. Розгортання та згортання даних

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

У попередньому розділі показано, як розгортати дані з пов’язаних таблиць.

Ви також можете стиснути його, щоб включити лише зведені дані на основі агрегації за вашим вибором, як-от сума, підрахунок або відсоток.

2. Сортування даних

Щоб відсортувати дані, ви можете швидко змінити порядок за зростанням або спаданням значень.

Натисніть стрілку вниз біля назви стовпця та виберіть «Сортувати за зростанням» або «Сортувати за спаданням».

3. Форматування даних

Вкладка «Перетворення» в редакторі Power Query містить багато параметрів.

Наприклад, розкривне меню «Формат» дозволяє змінити всі дані в стовпці або всю таблицю на верхній або нижній регістр.

4. Додавання та видалення стовпців

Power Query спрощує процес додавання, видалення та зміни стовпців.

Щоб видалити стовпець, просто клацніть правою кнопкою миші заголовок непотрібного стовпця та виберіть «Видалити». Це остаточно видалить стовпець і всі його дані з таблиці.

Щоб додати новий стовпець, виберіть наявний стовпець, у якому ви хочете розмістити новий. Потім скористайтеся функцією «Додати стовпець із прикладів», щоб створити спеціальний стовпець або виконати обчислення між двома чи більше стовпцями.

5. Зміна типів даних

Робота з узгодженими та точними типами даних має вирішальне значення для точного аналізу. Power Query дозволяє легко змінювати типи даних:

1. Клацніть правою кнопкою миші на заголовку стовпця.

2. Виберіть «Змінити тип».

3. Виберіть потрібний тип даних із запропонованого списку.

Зміна типів даних може бути особливо корисною при роботі з відсотковими даними.

Наприклад, вам може знадобитися перетворити десяткові крапки на відсотки або перетворити текстові відсотки на числові для легшого обчислення та порівняння.

Робота з формулами та мовою M

Використовуючи Power Query в Excel, ви зіткнетеся з мовою M, яка є основою створення складних формул, обробки даних і створення потужних запитів. У цьому розділі ви дізнаєтеся про:

• рядок формул

• код М

• основні елементи синтаксису мови M

Панель формул

Рядок формул у редакторі Power Query Excel — це область, де ви вводите свої формули та код M.

Щоб скористатися ним, перейдіть до редактора Power Query, вибравши запит на панелі «Запити та підключення». Панель формул розташована над панеллю попереднього перегляду даних.

Написання та зміна коду M

Щоб створювати універсальні та потужні запити в Excel, вам потрібно буде написати та змінити мову Power Query, відому як M-код. Ось кілька порад, як почати:

• Використовуйте панель Applied Steps для написання покрокового коду, що полегшує відстеження та змінення логіки запиту.

• Комбінуйте код M із таблицею Excel, зведеними таблицями та зовнішніми джерелами даних, як-от Azure і текстовими файлами, щоб створювати динамічні та автоматизовані звіти.

• Налаштуйте параметри запиту, щоб уточнити параметри завантаження даних і покращити продуктивність.

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

Щоб переглянути код запиту, виконайте такі дії:

1. Відкрийте запит у редакторі Power Query.

2. Натисніть «Розширений редактор» на вкладці «Домашня сторінка».

Це код нашої зразкової об’єднаної таблиці (ваша може виглядати інакше):

Що таке синтаксис мови M?

Розуміння синтаксису мови M має вирішальне значення для створення ефективних формул Power Query. Майте на увазі наступне:

• Мова M чутлива до регістру, тому будьте обережні з великими літерами.

• Використовуйте квадратні дужки [] для посилань на стовпці, записи та списки.

• Щоб посилатися на попередній крок у вашому запиті, використовуйте назву його змінної (наприклад, #”Назва кроку”).

• Виведіть крок формули запиту з оператором «in».

Отримавши досвід роботи з , ви відкриєте для себе нові способи трансформації та аналізу даних, що зробить ваші робочі книги Excel ще більш цінним активом.

Подивіться відео нижче, настав час взяти щітку для чищення та очистити свої брудні дані.

Як оновити підключення даних

Однією з ключових особливостей Power Query є можливість легко оновити підключення Power Query.

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

Якщо ви не бачите панелі праворуч від аркуша, ви можете відкрити її, перейшовши до таблиці даних і натиснувши «Запити та підключення».

Коли панель «Запити та підключення» відкрита, на ній перераховано всі запити. Виконайте такі дії:

1. Клацніть правою кнопкою миші на запиті, який потрібно оновити.

2. Виберіть Оновити в контекстному меню.

Це оновить робочу книгу останніми даними з підключених джерел даних.

Як інтегрувати Power Query із моделлю даних

Модель даних Excel — це потужна функція, яка дозволяє створювати інструменти бізнес-аналітики, наприклад зведені таблиці, на основі зв’язків даних.

Модель даних — це в основному прихована база даних, яка містить імпортовані дані та зв’язки між таблицями.

Це частина функції Power Pivot, вбудованої в поточні версії Excel. Можливо, вам доведеться інсталювати Power Pivot як надбудову, щоб мати доступ до нього в попередніх версіях).

Ви можете інтегрувати підключення даних Power Query до моделі даних, виконавши такі дії:

1. Завантажте дані за допомогою Power Query, як описано в попередніх розділах.

2. Замість того, щоб завантажувати його безпосередньо в робочу книгу, виберіть «Завантажити в» у меню «Завантажити».

3. Установіть прапорець «Додати ці дані до моделі даних» у діалоговому вікні «Імпорт даних».

Як автоматизувати за допомогою Power Query і VBA

Завдяки інтеграції VBA (Visual Basic for Applications) ви можете створювати спеціальні підключення до даних і автоматизувати кроки перетворення даних у Power Query.

Ви можете використати наявні навички роботи з VBA для створення розширених моделей даних, використовуючи дані з таких платформ, як Microsoft Access, SharePoint і Analysis Services.

Хоча Power Query не має вбудованої підтримки VBA, ви можете використовувати код VBA, щоб опосередковано відкривати, оновлювати та керувати запитами Power Query.

Це дозволяє створювати потужні автоматизовані робочі процеси з даними та оптимізувати процес аналізу даних.

Ось приклад коду, який оновлює потужний запит у поточній книзі.

:

Dim wb As Workbook, pq As Object

Встановіть wb = ThisWorkbook

Установіть pq = wb.Queries(“Імена клієнтів”)

 pq.Оновити

Ось код у макросі Excel VBA:

Розширені функції Power Query

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

Вбудовані засоби аналізу даних включають:

• Умовні стовпці для застосування складної логіки до ваших перетворень даних.

• Функції групування та агрегування, щоб узагальнити ваші дані в змістовний спосіб.

• Операції Pivot і Unpivot для зміни форми даних для полегшення аналізу.

Багато з цих функцій ви знайдете на вкладці «Перетворення» в редакторі Power Query.

Крім того, ви можете підключити Power Query до Business Intelligence для більш розширеного аналізу даних і параметрів візуалізації .

Співпраця та обмін

Обмін даними, запитами та перетвореннями з членами команди є важливим для ефективної співпраці.

Ви можете поділитися робочою книгою із запитами, зберігши її на мережевому диску, у SharePoint або в іншому спільному сховищі.

Ви також можете експортувати код M за одним запитом, виконавши такі дії:

1. Відредагуйте запит у редакторі Power Query.

2. Натисніть «Розширений редактор» на вкладці «Домашня сторінка».

3. Скопіюйте весь код M у вікні розширеного редактора.

4. Відкрийте текстовий редактор (наприклад, Блокнот) і вставте скопійований код M у текстовий редактор.

5. Збережіть текстовий файл і надішліть його колезі.

Давайте завершимо це

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

Ця цінна функція допомагає оптимізувати процес створення звітів, очищення даних і виконання комплексного аналізу ваших таблиць даних. Використання

Power Query зрештою економить ваш час і підвищує ефективність роботи з файлами Excel.