Power QueryУ надбудові Power Query передбачено кілька способів створення та завантаження надбудови Power Query до книги. У вікні " Параметри запиту " також можна встановити настройки завантаження запиту за замовчуванням.
Порада Щоб визначити, чи дані на аркуші сформовано Power Query, виділіть клітинку з даними. Якщо на стрічці з'явиться контекстна вкладка "Запит", це означатиме, що дані завантажено з Power Query.
Відомості про інтеграцію Power QueryPower Query у програму Excel
Знайте, у якому середовищі перебуваєте Power Query добре інтегроване в інтерфейс користувача Excel, особливо під час імпорту даних, роботи з підключеннями та редагування зведених таблиць, таблиць Excel та іменованих діапазонів. Щоб уникнути плутанини, важливо знати, у якому середовищі ви зараз перебуваєте (Excel чи Power Query).
| Знайомий аркуш, стрічка та сітка Excel | The Power Query EditorРедактор Power Query ribbon and data preview |
|---|---|
|
|
Наприклад, робота з даними на аркуші Excel принципово відрізняється від операції з Power Query. Крім того, підключені дані, які відображаються на аркуші Excel, можуть Power Query працювати за лаштунками цих даних. Ця проблема виникає лише під час завантаження даних на аркуш або модель даних з Power Query.
Перейменування вкладок аркушів Вкладки аркушів варто перейменовувати змістовно, особливо якщо їх багато. Особливо важливо розрізняти аркуш із даними й аркуш, завантажений із Редактор Power Query. Навіть якщо у вас лише два аркуші, один з яких містить таблицю Excel під назвою "Аркуш1", а інший містить запит, створений внаслідок імпорту таблиці Excel під назвою "Таблиця1", легко заплутатися. Радимо завжди змінювати назви вкладок аркушів за замовчуванням на зрозуміліші. Наприклад, перейменуйте Аркуш1 на DataTable , а Таблицю1 – на QueryTable. Тепер зрозуміло, яка вкладка містить дані, а яка – запит.
Створення запиту
Можна створити запит на основі імпортованих даних або створити пустий запит.
Створення запиту на основі імпортованих даних
Це найпоширеніший спосіб створення запиту.
- Імпортувати дані. в статті Імпорт даних із зовнішніх джерел);
- Виділіть клітинку в даних, а потім натиснітькнопку"Редагувати запит".>
Створення пустого запиту
Можливо, ви захочете просто почати з нуля. Це можна зробити двома наведеними нижче способами.
- Вибір даних>Отримання даних>з інших джерел>Пустий запит.
- Select Data>Get Data>Launch Power Query EditorРедактор Power Query.
На цьому етапі можна вручну додати кроки та формули, якщо ви добре знаєте мову формул Power Query M.
Крім того, можна перейти на вкладку "Основне ", а потім вибрати команду в групі " Новий запит ". Виконайте одну з наведених нижче дій.
- Виберіть "Нове джерело ", щоб додати джерело даних. Ця команда подібна до команди " Отримати>дані " на стрічці Excel.
- Виберіть елемент "Останні джерела ", щоб вибрати джерело даних, з яким ви працюєте. Ця команда подібна до команди"Останні джерела даних>" на стрічці Excel.
- Виберіть пункт "Ввести дані ", щоб ввести дані вручну. Можна вибрати цю команду, щоб випробувати Редактор Power Query незалежно від зовнішнього джерела даних.
Завантаження запиту
Припустивши, що запит правильний і не містить помилок, можна завантажити його назад до аркуша або моделі даних.
Завантаження запиту з Редактор Power Query
У редакторі Power Query EditorРедактор Power Query виконайте одну з таких дій:
Щоб завантажити до аркуша, виберіть Home>Close & LoadClose & Load>.
Щоб завантажити дані в модель даних, виберіть "Головна>& Завантажити& Завантажити> до".
У діалоговому вікні"Імпорт даних " виберіть " Додати дані до моделі даних".
Порада Іноді команда "Завантажити до" неактивна або відключена. Це може статися під час першого створення запиту в книзі. У такому випадку натисніть кнопку "Закрити" & "Завантажити" на новому аркуші, виберіть елемент "Запити даних>" &вкладку "Запити>зв'язків", клацніть правою кнопкою миші запит і виберіть команду "Завантажити до". Також на Редактор 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 виберіть Home>Close & LoadClose & Load>.
до моделі даних. У Редактор Power Query виберіть Home>Close & Load>Close & LoadTo.
За замовчуванням, коли завантажується один запит, Power Query завантажує запити на новий аркуш, а до моделі даних завантажується одночасно кілька запитів. Стандартну поведінку можна змінити для всіх книг або лише поточної. Якщо вибрати ці параметри, Power Query не змінить результати запиту на аркуші, дані моделі даних і зауваження.
Крім того, можна динамічно перевизначати настройки за промовчанням запиту за допомогою діалогового вікна "Імпорт ", яке відобразиться після натискання кнопки "Закрити" & "LoadTo".
Глобальні параметри, що застосовуються до всіх книг
In the Power Query EditorРедактор Power Query, виберіть File>Options and settings>Query Options.
У діалоговому вікні "Параметри запиту " ліворуч у розділі "ГЛОБАЛЬНО " виберіть пункт "Завантаження даних".
У розділі " Настройки завантаження запиту за промовчанням " виконайте такі дії:
- Виберіть "Використовувати стандартні настройки навантаження".
- Установіть прапорець "Указати настроювані параметри завантаження за замовчуванням", а потім установіть або зніміть прапорець "Завантажити в аркуш " або "Завантажити в модель даних".
Порада У нижній частині діалогового вікна ви можете натиснути кнопку "Відновити значення за замовчуванням ", щоб безпечно повернутися до стандартних параметрів.
Параметри книги, які застосовуються лише до поточної книги
У діалоговому вікні "Параметри запиту " ліворуч у розділі "ПОТОЧНА КНИГА " виберіть пункт "Завантаження даних".
Виконайте одну або кілька з таких дій:
У розділі "Виявлення типів" установіть або зніміть прапорець "Визначати типи стовпців і заголовки для неструктурованих джерел".
За замовчуванням їх потрібно виявляти. Зніміть цей прапорець, якщо ви хочете формувати дані самостійно.
У розділі «Зв'язки» встановіть або зніміть прапорець «Створювати зв'язки між таблицями» під час першого додавання до моделі даних.
Перед завантаженням до моделі даних за замовчуванням необхідно знайти наявні зв'язки між таблицями, як-от зовнішні ключі в реляційній базі даних, та імпортувати їх разом із даними. Зніміть цей прапорець, якщо хочете робити це самостійно.У розділі "Зв'язки" встановіть або зніміть прапорець "Оновлювати зв'язки" під час оновлення запитів, завантажених у модель даних.
За замовчуванням зв'язки не оновлюються. Під час оновлення запитів, уже завантажених до моделі даних, Power QueryPower Query знаходить наявні зв'язки між таблицями, наприклад зовнішні ключі в реляційній базі даних, і оновлює їх. Це може призвести до вилучення зв'язків, створених вручну після імпорту даних, або введення нових зв'язків. Однак, якщо ви хочете це зробити, виберіть відповідний параметр.
У розділі "Фонові дані" встановіть або зніміть прапорець "Дозволити попередній перегляд завантажувати дані у фоновому режимі".
За замовчуванням попередній перегляд даних завантажується у фоновому режимі. Зніміть цей прапорець, якщо потрібно переглянути всі дані відразу.
Додаткові відомості
Power QueryДовідка з надбудови Power Query для програми Excel