Відомості про Power Query в Excel

Існує чотири етапи використання 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 M в Розширений редактор.

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

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

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

• Проміжне додавання    Створює новий запит для кожної операції додавання.

• Вбудоване додавання    Додайте дані до наявного запиту, доки не досягнете остаточного результату.

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

Є два основні способи завантаження запитів до книги:

• У Редактор Power Query на вкладці Основне в групі Закрити можна скористатися командами Закрити та Завантажити.

• В області Запити книги Excel (вибіркові запити & Connections) можна клацнути правою кнопкою миші запит і вибрати команду Завантажити до.

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

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

Інтеграція даних get & Transform Data (тепер називається 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 2016 та 2019 для Windows

Ми додали той самий функціонал Get & Transform Data на основі технології 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.

Примітка    У Excel 2016 та Excel 2019 для Mac немає підтримки Power Query.

Data Catalog вилучення

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

• 1 серпня 2018 року ми припинили приєднання нових клієнтів до Data Catalog.

• 3 грудня 2018 року користувачам не вдалося надати спільний доступ до нових або оновлених запитів у Data Catalog.

• 4 березня 2019 року Data Catalog припинила роботу. Після цієї дати ми рекомендували завантажити спільні запити, щоб продовжити використовувати їх поза Data Catalog, скориставшись параметром Відкрити в області завдань Мої запити Data Catalog.

Power Query вилучення надбудов

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

Facebook списаних з'єднувача даних

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

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

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

• Power Query в Excel для Windows використовує .NET framework, але для цього потрібна версія 4.7.2 або пізніша. Докладні відомості див. в статті Оновлення .NET Framework.

• для Power Query в Excel для Windows потрібно, щоб середовище виконання WebView2 продовжиало підтримувати з'єднувач даних Web (Get Data from Web).  Докладні відомості див. в статті Завантаження середовища виконання WebView2.