Створення параметризований запит (Power Query)

Параметр дає змогу автоматично змінювати значення в запиті та не змінювати запит щоразу, щоб змінювати значення. Ви просто змінюєте значення параметра. Створений параметр зберігається в спеціальному параметризований запит, який можна зручно змінити безпосередньо з Програми Excel.

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

2. У Редактор Power Query виберіть Основне > Керування параметрами > Нові параметри.

3. У діалоговому вікні Керування параметрами натисніть кнопку Створити.

4. За потреби виконайте наведені нижче дії.

   Ім’я	Це має відображати функцію параметра, але залишити його якомога коротшим.
   Опис	Це може містити будь-які відомості, які допоможуть користувачам правильно використовувати цей параметр.
   Обов’язкові	Виконайте одну з таких дій: Будь-яке значення . Ви можете ввести будь-яке значення будь-якого типу даних у параметризований запит. Список значень    Ви можете обмежити значення певним списком, ввівши їх у маленьку сітку. Також потрібно вибрати значення за промовчанням і поточне значення нижче. Запит Виберіть запит списку, який нагадує структурований стовпець списку , розділений комами та взятий у фігурні дужки. Наприклад, поле стану питань може мати три значення: {"Створити", "Триває", "Закрито"}. Щоб створити запит списку заздалегідь, відкрийте Розширений редактор (виберіть основне > Розширений редактор), видаліть шаблон коду, введіть список значень у форматі списку запитів і натисніть кнопку Готово. Коли ви завершите створення параметра, запит списку відображатиметься у значеннях параметрів.
   Тип	Цей параметр визначає тип даних.
   Рекомендовані значення	За потреби додайте список значень або вкажіть запит для надання пропозицій щодо вводу.
   Значення за промовчанням	Цей параметр відображається, лише якщо для параметра Suggested Values (Запропоновані значення ) установлено значення List of values (Список значень) і вказано, який елемент списку використовуватиметься за замовчуванням. У такому разі потрібно вибрати стандартне значення.
   Поточне значення	Залежно від того, де використовується параметр, якщо цей параметр пустий, запит може не повертати результатів. Якщо вибрано параметр Обов'язковий , поточне значення не може бути пустим.

5. Щоб створити параметр, натисніть кнопку OK.

Ось як можна керувати змінами в розташуваннях джерел даних і запобігти помилкам оновлення. Наприклад, за умови подібної схеми та джерела даних створіть параметр, щоб легко змінити джерело даних і запобігти помилкам оновлення даних. Іноді змінюється сервер, база даних, папка, ім'я файлу або розташування. Можливо, диспетчер баз даних час від часу міняє сервер, щомісячне падіння CSV-файлів передається в іншу папку або вам потрібно легко переключатися між середовищем розробки, тестування/ виробництва.

Крок 1. Створення параметризований запит

У наведеному нижче прикладі є кілька CSV-файлів, які імпортуються за допомогою операції імпорту папки (Виберіть дані > Отримати дані > з файлів > з папки) з папки C:\DataFilesCSV1. Але іноді інша папка іноді використовується як розташування для видалення файлів C:\DataFilesCSV2. Параметр у запиті можна використовувати як значення для заміни іншої папки.

1. Виберіть Основне > Керування параметрами > Новий параметр.

2. Введіть такі відомості в діалоговому вікні Керування параметрами :

   Ім’я	CSVFileDrop
   Опис	Альтернативне розташування для перетягування файлів
   Обов’язкові	Так
   Тип	Текст
   Рекомендовані значення	Будь-яке значення
   Поточне значення	C:\DataFilesCSV1

3. Натисніть кнопку OK.

Крок 2. Додавання параметра до запиту даних

1. Щоб установити ім'я папки як параметр, у розділі Параметри запиту в розділі Кроки запиту виберіть пункт Джерело, а потім – Редагувати настройки.

2. Переконайтеся, що для параметра Шлях до файлу встановлено значення Параметр, а потім виберіть щойно створений параметр із розкривного списку.

3. Натисніть кнопку OK.

Крок 3. Оновлення значення параметра

Розташування папки щойно змінено, тому тепер можна просто оновити параметризований запит.

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

2. Введіть нове розташування в полі Поточне значення , наприклад C:\DataFilesCSV2.

3. Натисніть кнопку Основне > Закрити & Завантажити.

4. Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит даних за допомогою оновленого параметра (Виберіть дані > Оновити все).

Інколи потрібно легко змінити фільтр запиту, щоб отримати різні результати, не редагуючи запит або не роблячи дещо інші копії одного запиту. У цьому прикладі ми змінюємо дату, щоб зручно змінювати фільтр даних.

1. Щоб відкрити запит, знайдіть попередньо завантажену з Редактор Power Query, виділіть клітинку в даних і натисніть кнопку Запит > Редагувати. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.

2. Клацніть стрілку фільтра в заголовку будь-якого стовпця, щоб відфільтрувати дані, а потім виберіть команду фільтра, наприклад Фільтри дати й часу > Після. Відкриється діалогове вікно Фільтрування рядків .
   
   

3. Натисніть кнопку ліворуч від поля Значення , а потім виконайте одну з таких дій:

   • Щоб використати наявний параметр, виберіть Параметр, а потім виберіть потрібний параметр у списку, який з'явиться праворуч.

   • Щоб використати новий параметр, натисніть кнопку Створити параметр, а потім створіть параметр.

4. Введіть нову дату в полі Поточне значення , а потім виберіть основне > Закрити & Завантажити.

5. Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит даних за допомогою оновленого параметра (Виберіть дані > Оновити все). Наприклад, змініть значення фільтра на іншу дату, щоб переглянути нові результати.

6. Введіть нову дату в полі Поточне значення .

7. Натисніть кнопку Основне > Закрити & Завантажити.

8. Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит даних за допомогою оновленого параметра (Виберіть дані > Оновити все).

У цьому прикладі значення параметра запиту читається з клітинки в книзі. Не потрібно змінювати параметризований запит, просто оновіть значення клітинки. Наприклад, потрібно відфільтрувати стовпець за першою буквою, але легко змінити значення на будь-яку букву від А до Я.

1. На аркуші книги, де завантажується запит, який потрібно відфільтрувати, створіть таблицю Excel із двома клітинками: заголовком і значенням.
    

   Мій фільтр
   G

2. Виберіть клітинку в таблиці Excel, а потім натисніть кнопку Дані > Отримати дані > З таблиці або діапазону. З'явиться Редактор Power Query.

3. У полі Ім'я в області Параметри запиту праворуч змініть ім'я запиту на зрозуміліше, наприклад FilterCellValue. 

4. Щоб передати значення в таблиці, а не саму таблицю, клацніть правою кнопкою миші значення в поданні попереднього перегляду даних і виберіть пункт Деталізувати.

   Зверніть увагу, що формулу змінено на = #"Changed Type"{0}[MyFilter]

   Коли таблиця Excel використовується як фільтр на кроці 10, Power Query посилається на значення Таблиці як умову фільтра. Пряме посилання на таблицю Excel спричинить помилку.

5. Натисніть кнопку Основне > Закрити & Завантажити > Закрити & Завантажити до. Тепер у вас є параметр запиту з іменем "FilterCellValue", який використовується на кроці 12.

6. У діалоговому вікні Імпорт даних виберіть лише створити підключення, а потім натисніть кнопку OK.

7. Відкрийте запит, який потрібно відфільтрувати зі значенням у таблиці FilterCellValue, який попередньо завантажено з Редактор Power Query, вибравши клітинку в даних і вибравши пункт Запит > Редагувати. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.

8. Клацніть стрілку фільтра в заголовку будь-якого стовпця, щоб відфільтрувати дані, а потім виберіть команду фільтра, як-от Текстові фільтри > Починається з. Відкриється діалогове вікно Фільтрування рядків . 

9. Введіть будь-яке значення в полі Значення , наприклад "G", а потім натисніть кнопку OK. У цьому випадку значення є тимчасовим покажчиком місця заповнення для значення в таблиці FilterCellValue, введеного на наступному кроці.

10. Клацніть стрілку праворуч від рядка формул, щоб відобразити всю формулу. Ось приклад умови фільтрування у формулі:
    
    = Table.SelectRows(#"Змінений тип", кожен текст.StartsWith([Ім'я], "Г"))

11. Виберіть значення фільтра. У формулі виберіть "G".

12. За допомогою функції M Intellisense введіть кілька перших букв створеної таблиці FilterCellValue, а потім виберіть її зі списку, що з'явиться.

13. Натисніть кнопку Основне > Закрити > Закрити & Завантажити.

Результат

Тепер запит використовує значення в таблиці Excel, створеній для фільтрування результатів запиту. Щоб використати нове значення, відредагуйте вміст клітинки у вихідній таблиці Excel на кроці 1, змініть "G" на "V", а потім оновіть запит.

Можна вказати, чи дозволено параметризовані запити.

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

2. В області ліворуч у розділі ГЛОБАЛЬНИЙ виберіть Редактор Power Query.

3. В області праворуч у розділі Параметри встановіть або зніміть прапорець Завжди дозволяти параметризування в діалогових вікнах джерела даних і перетворення.