Про Power QueryPower Query у програмі Excel

За допомогою Power Query (відомої як "Отримати & перетворити" в Excel) можна імпортувати зовнішні дані або підключитися до них, а потім сформувати ці дані відповідно до своїх потреб, наприклад видалити стовпець, змінити тип даних або об'єднати таблиці. Потім можна завантажити запит в Excel для створення діаграм і звітів. Періодично можна оновлювати дані, щоб вони були актуальними. Power QueryНадбудова Power Query доступна в трьох програмах Excel: Excel для Windows, Excel для Mac і Excel для Інтернету. Зведення всіх Power Query розділів довідки див. в довідці Power Query для Excel.

Сучасне діалогове вікно "Отримати дані"

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

Каталог OneLake в Excel

Інтеграція каталогу OneLake в Excel дозволяє переглядати, керувати та підключатися до артефактів даних з каталогу OneLake Fabric безпосередньо в діалоговому вікні Modern Get Data. За допомогою каталогу OneLake ви можете легко знаходити та підключатися до високоякісних, схвалених, рекомендованих та нещодавно відкритих артефактів даних Fabric, таких як Lakehouse та Warehouses.
​​​

Щоб використовувати каталог OneLake в Excel, виконайте такі дії:

1. В Excel перейдіть на вкладку " Дані " на стрічці.
2. Клацніть « Отримати дані > » (попередній перегляд), щоб відкрити діалогове вікно «Сучасні параметри отримання даних».
3. Перегляньте доступні артефакти даних у розділі каталогу OneLake на вкладці "Основне ".
4. Щоб знайти більше артефактів даних, перейдіть на вкладку каталогу OneLake у діалоговому вікні.
5. Виберіть потрібний артефакт даних і дотримуйтеся вказівок, щоб підключитися й завантажити дані в Excel.

Докладні відомості див . в огляді каталогу OneLake.

Чотири етапи Power Query

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

1. Підключити Створюйте підключення до даних у хмарі, службі або локально
2. Перетворення Формування даних відповідно до власних потреб, при цьому вихідне джерело залишається незмінним
3. Об'єднання Інтегруйте дані з кількох джерел, щоб отримати унікальне подання даних
4. Завантажити Завершіть запит, завантажте його на аркуш або модель даних і періодично оновлюйте його.

У наведених нижче розділах кожен етап розглядається детальніше.

Підключення

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

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

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

Перетворювати дані означає змінювати їх певним чином відповідно до своїх потреб в аналізі даних. Наприклад, можна видалити стовпець, змінити тип даних або фільтрувати рядки. Кожна з цих операцій – це перетворення даних. Процес перетворення (і поєднання) до одного або кількох наборів даних також називається формуванням даних.

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

Power QueryУ надбудові Power Query використовується спеціальне вікно, яке називається Редактор Power QueryРедактор Power Query для полегшення та відображення перетворень даних. Щоб відкрити Редактор Power Query, натисніть кнопку "Запустити Редактор Power Query" з команди "Отримати дані" в групі "Отримати & перетворення даних", але ця також відкриватиметься після підключення до джерела даних, створення нового запиту або завантаження запиту.

Редактор Power Query стежить за всіма діями, які ви виконуєте з даними, записуючи й позначаючи кожне перетворення (крок), який застосовується до даних. Незалежно від того, чи це перетворення – підключення до даних, видалення стовпця, об'єднання або змінення типу даних – кожне перетворення можна переглянути й змінити в розділі "ЗАСТОСОВАНІ КРОКИ " в області "Параметри запиту ".

В інтерфейсі користувача можна виконати багато перетворень. Кожне перетворення записується як крок у фоновому режимі. Кроки можна навіть змінювати та записувати за допомогою мови Power Query M у Розширений редактор.

Усі перетворення, що застосовуються до зв'язків даних, разом складають запит, який є новим поданням вихідного (і незміненого) джерела даних. Коли ви оновлюєте запит, кожен крок виконується автоматично. Запити замінюють необхідність вручну підключати та формувати дані в програмі Excel.

Об'єднання

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

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

• Проміжне додавання Для кожної дії з додавання створюється новий запит.
• Вбудоване додавання Додає дані до наявного запиту до досягнення остаточного результату.

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

Завантажити

Запити в книгу можна завантажити двома основними способами.

• З Редактор Power Query можна використовувати команди "Закрити" та "Завантажити" у групі"Закрити" на вкладці "Основне".
• В області запитів книги Excel (" Вибіркові запити & зв'язки") можна клацнути запит правою кнопкою миші та вибрати команду "Завантажити до".

Крім того, ви можете точно настроїти параметри завантаження, використовуючи діалогове вікно "Параметри запиту" ("Вибір параметрів файлу>й настройок>", "Параметри запиту"), щоб вибрати спосіб перегляду даних і місце завантаження даних в аркуші чи моделі даних (яка є реляційним джерелом даних для кількох таблиць у книзі).

Підтримка програм для Power Query в Excel

Понад десять років Power Query підтримується в програмі Excel для Windows. Тепер програма Excel розширює Power Query підтримку в Excel для Mac, а також додає підтримку вебпрограми Excel. Це означає, що ми робимо Power Query доступним на трьох основних платформах і демонструємо популярність і функціональність Power Query серед клієнтів Excel. Слідкуйте за майбутніми оголошеннями на сторінці про стратегію Microsoft 365 і "Нові можливості Excel для Microsoft 365".

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

Журнал Power Query в Excel

Інтеграція функції "Отримати & перетворити дані" (зараз називається Power Query) у програму Excel зазнала значних змін.

Excel 2010 і 2013 для Windows

Вперше в програмі Excel 2010 для Windows введено Power Query, і вона була доступна як безкоштовна надбудова, яку можна завантажити тут: Завантажте надбудову Power Query. Коли Power Query функцію було ввімкнуто, вона була доступна на вкладці Power Query стрічки.

Microsoft 365

Оновлено Power Query – це основна можливість в Excel для імпорту та очищення даних. Майстри та знаряддя імпорту даних Power Query можна відкрити на вкладці "Дані" стрічки Excel у групі "Отримати & перетворити дані".

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

Цей новий спосіб також замінив старі майстри імпорту даних, які можна використовувати під керуванням команди " Дані " в групі " Отримання зовнішніх даних ". Проте доступ до них можна отримати з діалогового вікна "Параметри Excel" (Вибір параметрів файлу>>,Відображення>застарілих версій майстра імпорту даних).

Excel 2016Excel 2016 і 2019 for Windows

Ми додали такий самий функціонал "Отримати & перетворити дані" на основі технології Power Query, що й у Microsoft 365.

Excel для Microsoft 365 для Mac

У 2019 році ми почали підтримку Power Query в Excel для Mac. З тих пір ми додали можливість оновлювати запити Power Query з TXT, CSV, XLSX, JSON і XML файлів. Крім того, додано можливість оновлювати дані з SQL Server та таблиць & діапазонів у поточній книзі.

У жовтні 2019 р. додано можливість оновлювати наявні запити Power Query та використовувати VBA для створення й редагування нових запитів.

У січні 2021 року ми додали підтримку оновлення запитів Power Query із джерел OData та SharePoint.

Докладні відомості див. в статті "Використання Power Query в Excel для Mac".

Примітка Не підтримується Power Query в Excel 2016 і Excel 2019 для Mac.

Data CatalogData Catalog deprecation

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

• 1 серпня 2018 року ми припинили залучення нових клієнтів до Data Catalog.
• 3 грудня 2018 р. користувачам не дозволялося надавати спільний доступ до нових або оновлених запитів у каталозі даних Data CatalogData Catalog.
• 4 березня 2019 року Data Catalog припинив роботу. Після цієї дати рекомендовано завантажити свої спільні запити, щоб продовжити їх використання за межами каталогу даних каталогу даних, скориставшись параметром Open (Відкрити) в області завдань My Data CatalogData Catalog (Каталог даних MiData CatalogData Catalog Queries).

Power QueryВідхилення надбудови Power Query

На початку літа 2019 року ми офіційно припинили підтримку надбудови Power Query, необхідна для Excel 2010 і 2013 для Windows. З міркувань ввічливості ви можете використовувати надбудову, але згодом це може змінитися.

FacebookFacebook data connector retired

Імпорт і оновлення даних із Facebook в Excel припинили працювати у квітні 2020 року. Більше не працюють Facebook підключення, створені до цієї дати. Щоб уникнути неочікуваних результатів, радимо якнайшвидше переглянути або видалити всі наявні запити Power Query, які використовують з'єднувач Facebook.

Критичні оновлення Excel для Windows

З червня 2023 р. Power Query в Excel для Windows потребує таких компонентів:

• Power QueryНадбудова Power Query у програмі Excel для Windows використовує платформу .NET Framework, але для неї потрібна версія 4.7.2 або пізніша. Додаткові відомості див. в розділі Оновлення .NET Framework.NET Framework.
• Power QueryДля роботи надбудови Power Query в Excel для Windows потрібне середовище виконання WebView2, щоб і надалі підтримувати з'єднувач Data Web connector (Отримати дані з Інтернету).  Докладні відомості див. в статті "Завантаження середовища виконання WebView2".