Power Query пропонує кілька способів створення та завантаження запитів Power до книги. Також можна встановити параметри завантаження запиту за замовчуванням у вікні Параметри запиту .
Порада Щоб дізнатися, чи формуються дані на аркуші Power Query, виділіть клітинку даних і, якщо з'явиться контекстна стрічка Query, дані завантажено з Power Query.
Інтеграція Power Query з Excel
Дізнайтеся, яке середовище ви перебуваєте в Power Query добре інтегровано в інтерфейс користувача Excel, особливо коли ви імпортуєте дані, працюєте з підключеннями та редагуєте зведені таблиці, таблиці Excel і іменовані діапазони. Щоб уникнути плутанини, важливо в будь-який момент часу дізнатися, у якому середовищі ви перебуваєте, Excel або Power Query.
| Знайомий аркуш Excel, стрічка та сітка | Стрічка Редактор Power Query та попередній перегляд даних |
|---|---|
|
|
Наприклад, обробка даних на аркуші Excel кардинально відрізняється від Power Query. Крім того, підключені дані, які відображаються на аркуші Excel, можуть або не мають Power Query працювати за лаштунками, щоб сформувати дані. Це відбувається, лише коли ви завантажуєте дані на аркуш або модель даних із Power Query.
Перейменування вкладок аркушів Радимо перейменувати вкладки аркушів змістовно, особливо якщо їх багато. Особливо важливо уточнити різницю між аркушем даних і аркушем, завантаженим із Редактор Power Query. Навіть якщо у вас є лише два аркуші, один із таблицею Excel під назвою "Аркуш1", а інший запит, створений імпортом цієї таблиці Excel під назвою "Таблиця1", легко заплутатися. Радимо завжди змінювати стандартні імена вкладок аркушів на зрозуміліші для вас імена. Наприклад, перейменуйте Аркуш1 на DataTable і Table1 на QueryTable. Тепер зрозуміло, яка вкладка містить дані, а яка вкладка містить запит.
Створення запиту
Можна створити запит з імпортованих даних або створити пустий запит.
Створення запиту з імпортованих даних
Це найпоширеніший спосіб створення запиту.
- Імпорт деяких даних. в статті Імпорт даних із зовнішніх джерел);
- Виділіть клітинку в даних і натисніть кнопкуРедагуватизапит>.
Створення пустого запиту
Можна просто почати з нуля. Це можна зробити двома наведеними нижче способами.
- Виберіть параметр Дані>Отримати дані>з інших джерел>Пустий запит.
- Виберіть Редактор Power Query>Запускданих отримати дані>.
На цьому етапі ви можете вручну додати кроки та формули, якщо добре знаєте мову формул Power Query M.
Крім того, можна натиснути кнопку Основне , а потім вибрати команду в групі Новий запит . Виконайте одну з наведених нижче дій.
- Натисніть кнопку Створити джерело , щоб додати джерело даних. Ця команда схожа на команду "Отримати дані>" на стрічці Excel.
- Виберіть останні джерела , щоб вибрати джерело даних, з яким ви працювали. Ця команда схожа на командуОстанні джереладаних> на стрічці Excel.
- Натисніть кнопку Ввести дані , щоб ввести дані вручну. Ви можете вибрати цю команду, щоб спробувати Редактор Power Query незалежно від зовнішнього джерела даних.
Завантаження запиту
Якщо запит припустимий і не містить помилок, його можна завантажити назад на аркуш або модель даних.
Завантаження запиту з Редактор Power Query
У Редактор Power Query виконайте одну з таких дій:
Щоб завантажити аркуш, натисніть кнопку Закрити основне>& Завантажити>закрити & Завантажити.
Щоб завантажитися до моделі даних, натисніть кнопку Закрити головну>сторінку & Закрити>& Завантажити до.
У діалоговому вікніІмпорт даних натисніть кнопку Додати ці дані до моделі даних.
Порада Іноді команда Завантажити до неактивна або неактивна. Це може статися під час першого створення запиту в книзі. У такому разі натисніть кнопку Закрити & Завантажити, на новому аркуші виберіть Запитів даних>& вкладка Запити підключень>, клацніть правою кнопкою миші запит, а потім виберіть Завантажити до. Крім того, на стрічці Редактор Power Query виберіть Завантажити запит>до.
Завантаження запиту з області "Запити та підключення"
У програмі Excel може знадобитися завантажити запит до іншого аркуша або моделі даних.
- У програмі Excel виберіть запит даних>& підключення, а потім перейдіть на вкладку Запити .
- У списку запитів знайдіть запит, клацніть його правою кнопкою миші та виберіть Завантажити до. Відкриється діалогове вікно Імпорт даних .
- Вирішіть, як імпортувати дані, і натисніть кнопку OK. Щоб отримати додаткові відомості про використання цього діалогового вікна, виберіть знак питання (?).
Редагування запиту з аркуша
Змінити запит, завантажений на аркуш, можна кількома способами.
Редагування запиту з даних на аркуші Excel
- Щоб відредагувати запит, знайдіть попередньо завантажену з Редактор Power Query, виділіть клітинку в даних і натисніть кнопкуРедагуватизапит>.
Редагування запиту з області "Запити & підключення"
Область "Запити & підключення" зручніша для використання, якщо в одній книзі є багато запитів, і ви хочете швидко знайти її.
- У програмі Excel виберіть запит даних>& підключення, а потім перейдіть на вкладку Запити .
- У списку запитів знайдіть запит, клацніть правою кнопкою миші запит і виберіть Редагувати.
Редагування запиту в діалоговому вікні "Властивості запиту"
- У програмі Excel виберіть Дані даних> & запитипідключень>, клацніть правою кнопкою миші запит і виберіть пункт Властивості, у діалоговому вікні Властивості виберіть вкладку Визначення, а потім натисніть кнопку Редагувати запит.
Порада Якщо ви на аркуші із запитом, виберітьВластивостіданих>, у діалоговому вікні Властивості перейдіть на вкладку Визначення та натисніть кнопку Редагувати запит.
Редагування запиту таблиці в моделі даних
Модель даних зазвичай містить кілька таблиць, упорядкованих у зв'язок. Ви завантажуєте запит до моделі даних за допомогою команди Завантажити до , щоб відобразити діалогове вікно Імпорт даних , а потім установивши прапорець Додати ці дані до режиму данихl. Докладні відомості про моделі даних див. в статті Визначення джерел даних, які використовуються в моделі даних книги, Створення моделі даних у програмі Excel і Створення зведеної таблиці за допомогою кількох таблиць.
Щоб відкрити модель даних, виберіть елемент Керування Надбудовою Power Pivot>.
У нижній частині вікна Power Pivot виберіть вкладку аркуша потрібної таблиці.
Переконайтеся, що відображається правильна таблиця. Модель даних може містити багато таблиць.
Зверніть увагу на ім'я таблиці.
Щоб закрити вікно Power Pivot, натисніть кнопку Закрити файл>. Відновлення пам'яті може тривати кілька секунд.
Виберіть Зв'язки даних> & вкладкиЗапити властивостей>, клацніть правою кнопкою миші запит, а потім виберіть Редагувати.
Завершивши вносити зміни в Редактор Power Query, натисніть кнопку Закрити файл>& Завантажити.
Результат
Запит на аркуші та таблицю в моделі даних буде оновлено.
Завантаження запиту до моделі даних займає надзвичайно багато часу
Якщо ви помітили, що завантаження запиту до моделі даних триває набагато довше, ніж завантаження до аркуша, перевірте Power Query кроки, щоб дізнатися, чи фільтрується текстовий стовпець або структурований стовпець списку за допомогою оператора Містить. Ця дія призводить до того, що Excel перелічить весь набір даних для кожного рядка. Крім того, Excel не може ефективно використовувати багатопрочитане виконання. Щоб вирішити цю проблему, спробуйте використати інший оператор, наприклад Дорівнює або Починається з.
Корпорації Майкрософт відомо про цю проблему, і вона знаходиться під слідством.
Настроювання параметрів завантаження запиту
Ви можете завантажити Power Query:
До аркуша. У Редактор Power Query натисніть кнопку Закрити основне>& Завантажити>закрити & Завантажити.
До моделі даних. У Редактор Power Query натисніть кнопку Закрити основне>& Завантажити>закрити & Завантажитито.
За замовчуванням Power Query завантажує запити на новий аркуш під час завантаження одного запиту та завантажує кілька запитів одночасно до моделі даних. Можна змінити стандартну поведінку для всіх книг або лише поточної книги. Якщо встановити ці параметри, Power Query не змінює результати запиту на аркуші або дані моделі даних і зауваження.
Ви також можете динамічно перевизначити стандартні параметри запиту за допомогою діалогового вікна Імпорт , яке відобразиться після натискання кнопки Закрити & LoadTo.
Глобальні параметри, які застосовуються до всіх книг
У Редактор Power Query виберіть Параметри файлу>та параметри>Параметри запиту.
У діалоговому вікні Параметри запиту в області ліворуч у розділі GLOBAL ( Глобальний шаблон) виберіть Data Load (Завантаження даних).
У розділі Параметри завантаження запиту за промовчанням виконайте такі дії:
- Виберіть Використовувати стандартні настройки завантаження.
- Установіть прапорець Укажіть настроювані настройки завантаження за замовчуванням, а потім установіть або зніміть прапорець Завантажити до аркуша або Завантажити до моделі даних.
Порада У нижній частині діалогового вікна можна вибрати пункт Відновити за замовчуванням , щоб зручно повернутися до настройок за замовчуванням.
Параметри книги, які застосовуються лише до поточної книги
У діалоговому вікні Параметри запиту в області ліворуч у розділі CURRENT WORKBOOK (ПОТОЧНА КНИГА) виберіть Data Load (Завантаження даних).
Виконайте одну або кілька з таких дій:
У розділі Виявлення типів установіть або зніміть прапорець Визначати типи стовпців і заголовки неструктурованих джерел.
Стандартна поведінка полягає в тому, щоб виявити їх. Зніміть цей прапорець, якщо ви віддаєте перевагу формувати дані самостійно.
У розділі Зв'язки встановіть або зніміть прапорець Створювати зв'язки між таблицями під час першого додавання до моделі даних.
Перш ніж завантажуватися до моделі даних, за замовчуванням потрібно знайти наявні зв'язки між таблицями, наприклад зовнішні ключі у реляційній базі даних, і імпортувати їх із даними. Зніміть цей прапорець, якщо ви віддаєте перевагу цьому самостійно.У розділі Зв'язки встановіть або зніміть прапорець Оновлювати зв'язки під час оновлення запитів, завантажених до моделі даних.
За замовчуванням зв'язки не оновлюються. Під час оновлення запитів, уже завантажених до моделі даних, Power Query знаходить наявні зв'язки між таблицями, як-от зовнішні ключі в реляційній базі даних, і оновлює їх. Це може видалити зв'язки, створені вручну після імпорту даних, або ввести нові зв'язки. Однак, якщо потрібно зробити це, виберіть відповідний параметр.
У розділі Фонові дані встановіть або зніміть прапорець Дозволити попередній перегляд даних завантажувати у фоновому режимі.
Стандартна поведінка полягає в тому, щоб завантажувати попередній перегляд даних у фоновому режимі. Зніміть цей прапорець, щоб відразу бачити всі дані.