Створення, завантаження або редагування запиту в програмі Excel (Power Query)

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

Знайомий аркуш Excel, стрічка та сітка	Стрічка редактора Power Query та попередній перегляд даних

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

Перейменування вкладок аркушів    Радимо перейменувати вкладки аркушів змістовно, особливо якщо їх багато. Особливо важливо уточнити різницю між аркушем даних і аркушем, завантаженим із редактора Power Query. Навіть якщо у вас є лише два аркуші, один із таблицею Excel під назвою "Аркуш1", а інший запит, створений імпортом цієї таблиці Excel під назвою "Таблиця1", легко заплутатися. Радимо завжди змінювати стандартні імена вкладок аркушів на зрозуміліші для вас імена. Наприклад, перейменуйте Аркуш1 на DataTable і Table1 на QueryTable. Тепер зрозуміло, яка вкладка містить дані, а яка вкладка містить запит.

Можна створити запит з імпортованих даних або створити пустий запит.

Створення запиту з імпортованих даних

Це найпоширеніший спосіб створення запиту.

1. Імпорт деяких даних. в статті Імпорт даних із зовнішніх джерел);

2. Виділіть клітинку в даних і натисніть кнопку Запит > Редагувати.

Створення пустого запиту

Можна просто почати з нуля. Це можна зробити двома наведеними нижче способами.

• Виберіть елемент Дані > Отримати > данихз інших джерел > пустий запит.

• Виберіть Елемент Дані > Отримати дані > запустити редактор Power Query.

На цьому етапі ви можете вручну додати кроки та формули, якщо добре знаєте мову формул Power Query M.

Крім того, можна натиснути кнопку Основне , а потім вибрати команду в групі Новий запит . Виконайте одну з наведених нижче дій.

• Натисніть кнопку Створити джерело , щоб додати джерело даних. Ця команда схожа на команду Дані > Отримати дані на стрічці Excel.

• Виберіть останні джерела , щоб вибрати джерело даних, з яким ви працювали. Ця команда схожа на команду Дані > Останні джерела на стрічці Excel.

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

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

Завантаження запиту з редактора Power Query

У редакторі Power Query виконайте одну з таких дій:

• Щоб завантажити аркуш, натисніть кнопку Основне > Закрити & Завантажити > Закрити & Завантажити.

• Щоб завантажитися до моделі даних, натисніть кнопку Домашня > Закрити & Завантажити > Закрити & Завантажити до.
  
  У діалоговому вікні Імпорт даних натисніть кнопку Додати ці дані до моделі даних.

Порада   Іноді команда Завантажити до неактивна або неактивна. Це може статися під час першого створення запиту в книзі. У такому разі натисніть кнопку Закрити & Завантажити, на новому аркуші виберіть пункт Дані > Запити & Підключення > Запитів , клацніть запит правою кнопкою миші та виберіть Завантажити до. Крім того, на стрічці редактора Power Query натисніть кнопку Запит > Завантажити до.

Завантаження запиту з області "Запити та підключення" 

У програмі Excel може знадобитися завантажити запит до іншого аркуша або моделі даних.

1. У програмі Excel виберіть data > Query & Connections, а потім перейдіть на вкладку Запити .

2. У списку запитів знайдіть запит, клацніть його правою кнопкою миші та виберіть Завантажити до. Відкриється діалогове вікно Імпорт даних .

3. Вирішіть, як імпортувати дані, і натисніть кнопку OK. Щоб отримати додаткові відомості про використання цього діалогового вікна, виберіть знак питання (?).

Змінити запит, завантажений на аркуш, можна кількома способами.

Редагування запиту з даних на аркуші Excel

• Щоб відредагувати запит, знайдіть одну з попередньо завантажених у редакторі Power Query, виділіть клітинку в даних і натисніть кнопку Запит > Редагувати.

Редагування запиту з області "Запити & підключення"

Область "Запити & підключення" зручніша для використання, якщо в одній книзі є багато запитів, і ви хочете швидко знайти її.

1. У програмі Excel виберіть data > Query & Connections, а потім перейдіть на вкладку Запити .

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

Редагування запиту в діалоговому вікні "Властивості запиту"

• У програмі Excel виберіть Дані > Дані & Підключення > Запити, клацніть правою кнопкою миші запит і виберіть пункт Властивості, у діалоговому вікні Властивості виберіть вкладку Визначення, а потім натисніть кнопку Редагувати запит.

Кінчик    Якщо ви на аркуші із запитом, виберіть Пункт Дані > Властивості, у діалоговому вікні Властивості виберіть вкладку Визначення, а потім натисніть кнопку Редагувати запит. 

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

1. Щоб відкрити модель даних, виберіть Power Pivot > Керування.

2. У нижній частині вікна Power Pivot виберіть вкладку аркуша потрібної таблиці.
   
   Переконайтеся, що відображається правильна таблиця. Модель даних може містити багато таблиць.

3. Зверніть увагу на ім'я таблиці.

4. Щоб закрити вікно Power Pivot, натисніть кнопку Файл > Закрити. Відновлення пам'яті може тривати кілька секунд.

5. Виберіть пункт Підключення > даних & властивості > запитів , клацніть правою кнопкою миші запит і виберіть редагувати.

6. Завершивши вносити зміни в редакторі Power Query, натисніть кнопку Файл > Закрити & Завантажити.

Результат

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

Якщо ви помітили, що завантаження запиту до моделі даних триває набагато довше, ніж завантаження до аркуша, перевірте кроки Power Query, щоб дізнатися, чи фільтрується текстовий стовпець або структурований стовпець списку за допомогою оператора Містить . Ця дія призводить до того, що Excel перелічить весь набір даних для кожного рядка. Крім того, Excel не може ефективно використовувати багатопрочитане виконання. Щоб вирішити цю проблему, спробуйте використати інший оператор, наприклад Дорівнює або Починається з.

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

Ви можете завантажити надбудову Power Query:

• До аркуша. У редакторі Power Query натисніть кнопку Основне > Закрити & Завантажити > Закрити & Завантажити.

• До моделі даних. У редакторі Power Query натисніть кнопку Основне > Закрити & Завантажити > Закрити & Завантажити до.
  
  За замовчуванням надбудова Power Query завантажує запити на новий аркуш під час завантаження одного запиту та завантажує кілька запитів одночасно до моделі даних.  Можна змінити стандартну поведінку для всіх книг або лише поточної книги. Коли ви налаштовуєте ці параметри, power Query не змінює результати запиту на аркуші або дані моделі даних і зауваження.
  
  Ви також можете динамічно перевизначити стандартні параметри запиту за допомогою діалогового вікна Імпорт , яке відобразиться після натискання кнопки Закрити & Завантажити до.

Глобальні параметри, які застосовуються до всіх книг

1. У редакторі Power Query виберіть Параметри та параметри> файлів > параметри запиту.

2. У діалоговому вікні Параметри запиту в області ліворуч у розділі GLOBAL (Глобальний шаблон) виберіть Data Load (Завантаження даних).

3. У розділі Параметри завантаження запиту за промовчанням виконайте такі дії:

   • Виберіть Використовувати стандартні настройки завантаження. 

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

Кінчик    У нижній частині діалогового вікна можна вибрати пункт Відновити за замовчуванням , щоб зручно повернутися до настройок за замовчуванням.

Параметри книги, які застосовуються лише до поточної книги

1. У діалоговому вікні Параметри запиту в області ліворуч у розділі CURRENT WORKBOOK (ПОТОЧНА КНИГА) виберіть Data Load (Завантаження даних).

2. Виконайте одну або кілька з таких дій:

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

   • У розділі Зв'язки встановіть або зніміть прапорець Створювати зв'язки між таблицями під час першого додавання до моделі даних.

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

   • У розділі Зв'язки встановіть або зніміть прапорець Оновлювати зв'язки під час оновлення запитів, завантажених до моделі даних.
     
     За замовчуванням зв'язки не оновлюються. Під час оновлення запитів, уже завантажених до моделі даних, надбудова Power Query знаходить наявні зв'язки між таблицями, наприклад зовнішніми ключами у реляційній базі даних, і оновлює їх. Це може видалити зв'язки, створені вручну після імпорту даних, або ввести нові зв'язки. Однак, якщо потрібно зробити це, виберіть відповідний параметр.

   • У розділі Фонові дані встановіть або зніміть прапорець Дозволити попередній перегляд даних завантажувати у фоновому режимі.
     
     Стандартна поведінка полягає в тому, щоб завантажувати попередній перегляд даних у фоновому режимі. Зніміть цей прапорець, щоб відразу бачити всі дані.