Power Query – інструмент Excel для роботи з даними

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

Основні функції Power Query

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

Як встановити Power Query

Якщо ви використовуєте MS Excel 2016 та новіший, Power Query вже вбудований в редактор і доступний через вкладку «Дані».

Джерело: автор статті

Для MS Excel 2010 та 2013 його потрібно завантажити та встановити.

1. Визначте тип системи: 32-розрядна (x86) або 64-розрядна (x64).
2. Завантажте відповідний файл.

1. Запустіть завантажений інсталятор MSI і дотримуйтесь інструкцій.
2. Після інсталяції в Excel з'явиться вкладка Power Query. Наприклад, так це виглядає у MS Excel 2010.

1. Power Query готовий до використання.

Далі у прикладах будемо працювати у MS Excel 2019.

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

Допустимо, у вас є джерело даних, які вам потрібно отримати та обробити.

1. У вкладці «Дані» переходимо до розділу «Отримати та перетворити дані».
2. Натискаємо кнопку «Отримати дані».
3. Тут можна вибрати джерело з основних категорій:

Файл: Excel, текстові файли, CSV, XML, JSON.

База даних: SQL Server, Access, Oracle, PostgreSQL.

Веб-служби: SharePoint, Dynamics 365, веб-API.

Azure (хмарна платформа): база даних, сховище таблиць.

Інші джерела: OData, ODBC, веб-сторінки.

Джерело: автор статті

Доступні типи даних

Power Query підтримує такі формати даних:

.csv (Comma-Separated Values, текстові файли з табличними даними)
.txt (текстові файли з роздільниками, наприклад, табуляцією)
.prn (файли з поділом на рядки)

OData (Open Data Protocol, відкритий веб-протокол)
Web (дані з веб-сторінок)
Salesforce та інші онлайн-сервіси через API

Як працювати у редакторі Power Query

Вилучення

Коли ви вибрали джерело, дані потрібно витягти. Наприклад візьмемо іншу таблицю Excel. Натискаємо:

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

Джерело: автор статті

Натискаємо на опцію завантаження "Завантажити в ...". Відкриється вікно імпорту даних.

Джерело: автор статті

У цьому вікні потрібно вибрати:

- Способи подання даних

Стандартний спосіб відображення даних. Завантажує дані у вигляді таблиці.

Створює нову зведену таблицю з урахуванням завантажених даних.

Створює зведену діаграму з урахуванням завантажених даних.

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

- Куди помістити дані

Дозволяє вибрати існуючий діапазон на аркуші, куди будуть завантажені дані.Так можна замінити або доповнити вже наявні дані.

Завантажує дані у новий аркуш.

— Чи додавати дані до моделі даних

Якщо поставити галочку, дані будуть також завантажені в модель даних Excel. ЦейWorkbookDataModel. Це корисно, якщо потрібно створити зведені таблиці або використовувати DAX для аналізу даних, а також об'єднувати дані з різних джерел.

Після того як ви налаштуєте завантаження та натисніть «ОК», завантажений елемент з'явиться на панелі «Запити та підключення» у вкладці «Запити».

Джерело: автор статті

У вкладці «Підключення» буде список всіх активних підключень до джерел даних, які Excel використовує для отримання інформації. Наприклад, модель ThisWorkbookDataModel буде там.

Джерело: автор статті

Після імпорту даних їх можна перетворити за допомогою редактора Power Query. Для цього натисніть подвійним клацанням.

Джерело: автор статті

Основні розділи редактора:

1. Панель редактора з інструментами та командами у різних вкладках.
2. Список запитів у поточній робочій книзі.
3. Рядок формул мовою M, про яку розповімо нижче.
4. Попередній перегляд даних.
5. Властивості з ім'ям запиту та додатковими параметрами "Всі властивості".
6. Застосовані кроки чи історія перетворення.

Перетворення

Якщо в завантаженій таблиці назви стовпців некоректні, їх можна змінити.

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

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

Зверніть увагу, що в прикладі завантаження даних з джерела завантажилися також і порожні рядки.Вони позначені як null.

Щоб відсортовані дані були коректними, слід виключити (NULL) із фільтра.

Джерело: автор статті

Що стосується мови M, він використовується для опису кроків.

Джерело: автор статті

Наприклад, коли ми застосовуємо фільтрацію, то у рядку з'явиться така функція:

= Table.SelectRows(#"Підвищені заголовки", each ([січень] <> null))

Table.SelectRows: функція, яка вибирає рядки з таблиці на основі заданої умови. Вона приймає два аргументи:

- першу частину (у даному випадку таблицю, з якої будуть обрані рядки);

- Другу частину (умова для вибору рядків).

#"Підвищені заголовки": посилання на попередній крок у запиті, який створює таблицю з підвищеними заголовками (тобто перший рядок таблиці використовується як заголовки колонок).

Станьте аналітиком даних та отримайте затребувану спеціальність

each ([січень] <> null): умова для фільтрації рядків.

each — це спеціальне ключове слово в мові M, яке дозволяє застосовувати умову для кожного рядка таблиці.

([січень] <> null) — це логічний вираз, який перевіряє, що значення в колонці «січень» не є null (тобто осередок не порожній).

У осередках також можна замінювати дані.

Джерело: автор статті

Після натискання на кнопку «Заміна значень» відкриється вікно для введення нового значення.

У вкладці «Додавання стовпця» є способи додавання стовпців.

Джерело: автор статті

Ви можете вибрати один з таких варіантів:

«Стовпець із прикладів»: дозволяє створити стовпець, заснований на введених вручну даних.

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

«Стовпець, що налаштовується»: дозволяє створити новий стовпець на основі формул.

Наприклад, у вас є таблиця з двома стовпцями: «Ціна» та «Кількість». Ви хочете створити новий стовпець «Сума», який обчислюватиме загальну вартість.

Формула для стовпця, що настроюється, може виглядати так:

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

«Стовпець індексу»: додає індексний стовпець, який міститиме послідовні номери для кожного рядка.

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

Після додавання стовпця індексу ваша таблиця може виглядати так:

Індекс допомагає швидко ідентифікувати кожен рядок у таблиці.

«Умовний стовпець»: додає новий стовпець на основі заданої умови.

Наприклад, у вас є стовпець "Бали". Ви хочете створити новий стовпець «Статус», який міститиме значення «Пройшов» для 60 балів і вище та «Не пройшов» для решти балів.

Джерело: автор статті

Умовний стовпець можна створити за допомогою такої умови:

Якщо [Бали] >= 60, то "Пройшов", інакше "Не пройшов"

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

Об'єднання даних

Power Query можна з'єднувати та об'єднувати таблиці з різних джерел, щоб створити єдиний набір даних.

Таблиці виглядають так:

1. У вкладці "Головна" є кнопка "Об'єднати запити".Натискаємо «Об'єднати запити до нового».

1. Відкриється вікно "Злиття". Вибираємо таблиці, які хочемо об'єднати, та тип даних.

У списку ви можете вибрати різні типи об'єднання:

- Зовнішнє з'єднання зліва поверне всі рядки з першої таблиці та відповідні дані з другої (якщо вони є).

- Зовнішнє з'єднання справа поверне всі рядки з другої таблиці та відповідні дані з першої.

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

- Внутрішнє з'єднання поверне ті рядки, де значення присутня в обох таблицях.

- Антиз'єднання зліва вибере всі рядки з першої таблиці, які мають відповідних даних у другій таблиці.

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

1. Як приклад виберемо внутрішнє з'єднання і виділимо стовпці, якими шукатимемо збіги.

Отримаємо результат злиття.

1. Натисніть «Закрити та завантажити». Об'єднана таблиця з'явиться на новому аркуші "Злиття1".

Автоматизація

Якщо дані у вихідних файлах оновлюються (наприклад, нові рядки додаються до таблиць), виконані кроки можна автоматично застосувати до нових даних.

Після змін у джерелі даних досить просто натиснути кнопку «Оновити» на елементах у «Запитах та підключеннях».

Джерело: автор статті

Якщо зміни відбулися в таблиці «Лист1», то оновлюємо її та залежну від неї таблицю – «Злиття1».

Робота з типами даних

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

У вкладці "Головна" редактора є кнопка "Тип даних".Якщо тип вказаний неправильний, потрібно виділити потрібний стовпець і застосувати правильний тип.

Джерело: автор статті

Як використовувати мову M

Для розширення можливостей Power Query запити можна складати одразу мовою M, а не через інструменти інтерфейсу. Розглянемо приклади.

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

Приклад: складна умова з кількома перевірками.

Table.AddColumn(Джерело, «Категорія», each if [Продаж] > 10000 then «Високі» elseif [Продаж] > 5000 then «Середні» else «Низькі»)

Цей рядок додає новий стовпець, де рядки класифікуються як «Високі», «Середні» або «Низькі» залежно від значення у стовпці «Продажі».

Робота зі списками та записами (List та Record).

Приклад: видалення рядків, що містять лише порожні значення.

Table.SelectRows(Джерело, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), )))

Запит фільтрує рядки, де всі значення не порожні.

Інтерфейс Power Query не дозволяє динамічно генерувати списки чи діапазони значень. Для цього доведеться користуватися синтаксисом M.

Приклад: Створення послідовного списку чисел від 1 до 100.

List.Generate(() => 1, each _

Створює перелік чисел від 1 до 100.

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

Приклад: створення користувальницької функції до розрахунку ПДВ.

Функція розраховує ПДВ з урахуванням переданого значення. Функції користувача можна викликати у формулі:

Table.AddColumn(Джерело, «ПДВ», each myVAT([Ціна]))

Фільтрування за допомогою вкладених запитів.

Приклад: фільтрація на основі значень, розрахованих в іншій таблиці.

Table.SelectRows(Джерело, each [ID] = Table.First(Table.SelectRows(ІншаТаблиця, each [Ім'я] = "Олексій"))[ID])

Цей запит знаходить рядки, де ID збігається з першим рядком іншої таблиці.

• Робота з кількома таблицями через вкладені запити

Можна виконувати операції над кількома таблицями одночасно.

Приклад: звернення до іншої таблиці усередині запиту.

Table.SelectRows(Джерело, each List.Contains(Table.Column(ІншаТаблиця, "ID"), [ID]))

Вибирає рядки, де ID із однієї таблиці зустрічаються в іншій таблиці.

Генерація випадкових чисел.

Приклад: генерація 10 випадкових значень.

List.Transform(, each Номер.RoundDown(Номер.RandomBetween(1, 100)))

Генерує список із 10 випадкових чисел між 1 і 100.

Підіб'ємо підсумок

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

Аналітики впливають на зростання бізнесу. Вони з'ясовують, який товар і коли більше купують. Вважають юніт-економіку. Оцінюють окупність рекламної кампанії. Тому компанії шукають та переманюють таких фахівців.

Power Query – інструмент Excel для роботи з даними

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

Основні функції Power Query

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

Як встановити Power Query

Якщо ви використовуєте MS Excel 2016 та новіший, Power Query вже вбудований в редактор і доступний через вкладку «Дані».

Джерело: автор статті

Для MS Excel 2010 та 2013 його потрібно завантажити та встановити.

1. Визначте тип системи: 32-розрядна (x86) або 64-розрядна (x64).
2. Завантажте відповідний файл.

1. Запустіть завантажений інсталятор MSI і дотримуйтесь інструкцій.
2. Після інсталяції в Excel з'явиться вкладка Power Query. Наприклад, так це виглядає у MS Excel 2010.

1. Power Query готовий до використання.

Далі у прикладах будемо працювати у MS Excel 2019.

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

Допустимо, у вас є джерело даних, які вам потрібно отримати та обробити.

1. У вкладці «Дані» переходимо до розділу «Отримати та перетворити дані».
2. Натискаємо кнопку «Отримати дані».
3. Тут можна вибрати джерело з основних категорій:

Файл: Excel, текстові файли, CSV, XML, JSON.

База даних: SQL Server, Access, Oracle, PostgreSQL.

Веб-служби: SharePoint, Dynamics 365, веб-API.

Azure (хмарна платформа): база даних, сховище таблиць.

Інші джерела: OData, ODBC, веб-сторінки.

Джерело: автор статті

Доступні типи даних

Power Query підтримує такі формати даних:

.csv (Comma-Separated Values, текстові файли з табличними даними)
.txt (текстові файли з роздільниками, наприклад, табуляцією)
.prn (файли з поділом на рядки)

OData (Open Data Protocol, відкритий веб-протокол)
Web (дані з веб-сторінок)
Salesforce та інші онлайн-сервіси через API

Як працювати у редакторі Power Query

Вилучення

Коли ви вибрали джерело, дані потрібно витягти. Наприклад візьмемо іншу таблицю Excel. Натискаємо:

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

Джерело: автор статті

Натискаємо на опцію завантаження "Завантажити в ...". Відкриється вікно імпорту даних.

Джерело: автор статті

У цьому вікні потрібно вибрати:

- Способи подання даних

Стандартний спосіб відображення даних. Завантажує дані у вигляді таблиці.

Створює нову зведену таблицю з урахуванням завантажених даних.

Створює зведену діаграму з урахуванням завантажених даних.

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

- Куди помістити дані

Дозволяє вибрати існуючий діапазон на аркуші, куди будуть завантажені дані. Так можна замінити або доповнити вже наявні дані.

Завантажує дані у новий аркуш.

— Чи додавати дані до моделі даних

Якщо поставити галочку, дані будуть також завантажені в модель даних Excel. ЦейWorkbookDataModel. Це корисно, якщо потрібно створити зведені таблиці або використовувати DAX для аналізу даних, а також об'єднувати дані з різних джерел.

Після того як ви налаштуєте завантаження та натисніть «ОК», завантажений елемент з'явиться на панелі «Запити та підключення» у вкладці «Запити».

Джерело: автор статті

У вкладці «Підключення» буде список всіх активних підключень до джерел даних, які Excel використовує для отримання інформації. Наприклад, модель ThisWorkbookDataModel буде там.

Джерело: автор статті

Після імпорту даних їх можна перетворити за допомогою редактора Power Query. Для цього натисніть подвійним клацанням.

Джерело: автор статті

Основні розділи редактора:

1. Панель редактора з інструментами та командами у різних вкладках.
2. Список запитів у поточній робочій книзі.
3. Рядок формул мовою M, про яку розповімо нижче.
4. Попередній перегляд даних.
5. Властивості з ім'ям запиту та додатковими параметрами "Всі властивості".
6. Застосовані кроки чи історія перетворення.

Перетворення

Якщо в завантаженій таблиці назви стовпців некоректні, їх можна змінити.

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

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

Зверніть увагу, що в прикладі завантаження даних з джерела завантажилися також і порожні рядки. Вони позначені як null.

Щоб відсортовані дані були коректними, слід виключити (NULL) із фільтра.

Джерело: автор статті

Що стосується мови M, він використовується для опису кроків.

Джерело: автор статті

Наприклад, коли ми застосовуємо фільтрацію, то у рядку з'явиться така функція:

= Table.SelectRows(#"Підвищені заголовки", each ([січень] <> null))

Table.SelectRows: функція, яка вибирає рядки з таблиці на основі заданої умови. Вона приймає два аргументи:

- першу частину (у даному випадку таблицю, з якої будуть обрані рядки);

- Другу частину (умова для вибору рядків).

#"Підвищені заголовки": посилання на попередній крок у запиті, який створює таблицю з підвищеними заголовками (тобто перший рядок таблиці використовується як заголовки колонок).

Станьте аналітиком даних та отримайте затребувану спеціальність

each ([січень] <> null): умова для фільтрації рядків.

each — це спеціальне ключове слово в мові M, яке дозволяє застосовувати умову для кожного рядка таблиці.

([січень] <> null) — це логічний вираз, який перевіряє, що значення в колонці «січень» не є null (тобто осередок не порожній).

У осередках також можна замінювати дані.

Джерело: автор статті

Після натискання на кнопку «Заміна значень» відкриється вікно для введення нового значення.

У вкладці «Додавання стовпця» є способи додавання стовпців.

Джерело: автор статті

Ви можете вибрати один з таких варіантів:

«Стовпець із прикладів»: дозволяє створити стовпець, заснований на введених вручну даних.

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

«Стовпець, що налаштовується»: дозволяє створити новий стовпець на основі формул.

Наприклад, у вас є таблиця з двома стовпцями: «Ціна» та «Кількість». Ви хочете створити новий стовпець «Сума», який обчислюватиме загальну вартість.

Формула для стовпця, що настроюється, може виглядати так:

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

«Стовпець індексу»: додає індексний стовпець, який міститиме послідовні номери для кожного рядка.

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

Після додавання стовпця індексу ваша таблиця може виглядати так:

Індекс допомагає швидко ідентифікувати кожен рядок у таблиці.

«Умовний стовпець»: додає новий стовпець на основі заданої умови.

Наприклад, у вас є стовпець "Бали". Ви хочете створити новий стовпець «Статус», який міститиме значення «Пройшов» для 60 балів і вище та «Не пройшов» для решти балів.

Джерело: автор статті

Умовний стовпець можна створити за допомогою такої умови:

Якщо [Бали] >= 60, то "Пройшов", інакше "Не пройшов"

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

Об'єднання даних

Power Query можна з'єднувати та об'єднувати таблиці з різних джерел, щоб створити єдиний набір даних.

Таблиці виглядають так:

1. У вкладці "Головна" є кнопка "Об'єднати запити". Натискаємо «Об'єднати запити до нового».

1. Відкриється вікно "Злиття". Вибираємо таблиці, які хочемо об'єднати, та тип даних.

У списку ви можете вибрати різні типи об'єднання:

- Зовнішнє з'єднання зліва поверне всі рядки з першої таблиці та відповідні дані з другої (якщо вони є).

- Зовнішнє з'єднання справа поверне всі рядки з другої таблиці та відповідні дані з першої.

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

- Внутрішнє з'єднання поверне ті рядки, де значення присутня в обох таблицях.

- Антиз'єднання зліва вибере всі рядки з першої таблиці, які мають відповідних даних у другій таблиці.

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

1. Як приклад виберемо внутрішнє з'єднання і виділимо стовпці, якими шукатимемо збіги.

Отримаємо результат злиття.

1. Натисніть «Закрити та завантажити». Об'єднана таблиця з'явиться на новому аркуші "Злиття1".

Автоматизація

Якщо дані у вихідних файлах оновлюються (наприклад, нові рядки додаються до таблиць), виконані кроки можна автоматично застосувати до нових даних.

Після змін у джерелі даних досить просто натиснути кнопку «Оновити» на елементах у «Запитах та підключеннях».

Джерело: автор статті

Якщо зміни відбулися в таблиці «Лист1», то оновлюємо її та залежну від неї таблицю – «Злиття1».

Робота з типами даних

Power Query у кожного стовпця має бути заданий правильний тип даних для коректної роботи з ним.

У вкладці "Головна" редактора є кнопка "Тип даних". Якщо тип вказаний неправильний, потрібно виділити потрібний стовпець і застосувати правильний тип.

Джерело: автор статті

Як використовувати мову M

Для розширення можливостей Power Query запити можна складати одразу мовою M, а не через інструменти інтерфейсу. Розглянемо приклади.

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

Приклад: складна умова з кількома перевірками.

Table.AddColumn(Джерело, «Категорія», each if [Продаж] > 10000 then «Високі» elseif [Продаж] > 5000 then «Середні» else «Низькі»)

Цей рядок додає новий стовпець, де рядки класифікуються як «Високі», «Середні» або «Низькі» залежно від значення у стовпці «Продажі».

Робота зі списками та записами (List та Record).

Приклад: видалення рядків, що містять лише порожні значення.

Table.SelectRows(Джерело, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), )))

Запит фільтрує рядки, де всі значення не порожні.

Інтерфейс Power Query не дозволяє динамічно генерувати списки чи діапазони значень. Для цього доведеться користуватися синтаксисом M.

Приклад: Створення послідовного списку чисел від 1 до 100.

List.Generate(() => 1, each _

Створює перелік чисел від 1 до 100.

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

Приклад: створення користувальницької функції до розрахунку ПДВ.

Функція розраховує ПДВ з урахуванням переданого значення. Функції користувача можна викликати у формулі:

Table.AddColumn(Джерело, «ПДВ», each myVAT([Ціна]))

Фільтрування за допомогою вкладених запитів.

Приклад: фільтрація на основі значень, розрахованих в іншій таблиці.

Table.SelectRows(Джерело, each [ID] = Table.First(Table.SelectRows(ІншаТаблиця, each [Ім'я] = "Олексій"))[ID])

Цей запит знаходить рядки, де ID збігається з першим рядком іншої таблиці.

• Робота з кількома таблицями через вкладені запити

Можна виконувати операції над кількома таблицями одночасно.

Приклад: звернення до іншої таблиці усередині запиту.

Table.SelectRows(Джерело, each List.Contains(Table.Column(ІншаТаблиця, "ID"), [ID]))

Вибирає рядки, де ID із однієї таблиці зустрічаються в іншій таблиці.

Генерація випадкових чисел.

Приклад: генерація 10 випадкових значень.

List.Transform(, each Номер.RoundDown(Номер.RandomBetween(1, 100)))

Генерує список із 10 випадкових чисел між 1 і 100.

Підіб'ємо підсумок

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

Аналітики впливають на зростання бізнесу. Вони з'ясовують, який товар і коли більше купують. Вважають юніт-економіку. Оцінюють окупність рекламної кампанії. Тому компанії шукають та переманюють таких фахівців.