Можливо, ви добре знайомі з параметризованими запитами та їх використанням у SQL або Microsoft Query. Однак Power Query параметри мають ключові відмінності:
- Параметри можна використовувати на будь-якому кроці запиту. Окрім функції фільтра даних, параметри можна використовувати не лише для визначення шляхів до файлів чи імені сервера.
- Параметри не пропонують введення даних. Натомість можна швидко змінити їх значення за допомогою Power Query. В Excel можна навіть зберігати й отримувати значення з клітинок.
- Параметри зберігаються в простому параметризованому запиті, але вони відокремлені від запитів даних, у яких вони використовуються. Створивши параметр, до запитів за потреби можна додати параметр.
Примітка Якщо потрібно створити параметризовані запити іншим способом, див . статтю "Створення параметризованого запиту в Microsoft Query".
Створення параметра
За допомогою параметра можна автоматично змінювати значення в запиті, не змінюючи щоразу запит для змінення значення. Потрібно просто змінити значення параметра. Створений параметр зберігається в спеціальному параметризованому запиті, який можна легко змінити безпосередньо в Excel.
Select Data>Get Data>Other Sources>Launch Power Query EditorРедактор Power Query.
In the Power Query EditorРедактор Power Query, виберіть Home>Manage Parameters New > Parameters.
У діалоговому вікні "Керування параметрами " натисніть кнопку "Створити".
За потреби налаштуйте такі параметри:
Ім’я Він має відображати функцію параметра, але зробити його якомога коротшим. Опис У ньому можуть міститися будь-які відомості, які допоможуть користувачам правильно використовувати параметр. Обов'язковий Виконайте одну з таких дій:
Будь-яке значення У параметризований запит можна ввести будь-яке значення будь-якого типу.
Список значень Можна обмежити значення певним списком, ввівши їх у невелику сітку. Потрібно також вибрати значення за замовчуванням і поточне значення нижче.
Запит Виберіть запит списку, який має вигляд структурованого стовпця списку , розділеного комами та взятого у фігурні дужки.
Наприклад, поле стану питань може мати три значення: {"Нові", "Триваючі", "Закриті"}. Його потрібно спочатку створити. Для цього потрібно відкрити Розширений редактор (виберіть "Основне>Розширений редактор), видалити шаблон коду, ввести список значень у форматі списку запиту, а потім натиснути кнопку "Готово".
Коли ви завершите створення параметра, запит на список відобразиться в значеннях параметрів.Тип Цей параметр визначає тип даних параметра. Рекомендовані значення За бажанням додайте список значень або вкажіть запит для надання пропозицій щодо введення. Значення за замовчуванням Цей параметр відображається, лише якщо для параметра "Пропоновані значення " встановлено значення " Список значень" і вказано, який елемент списку використовується за замовчуванням. У цьому випадку потрібно вибрати стандартний параметр. Поточне значення Залежно від того, де використовується параметр, якщо цей параметр пустий, запит може не повернути жодних результатів. Якщо вибрано параметр «Обов'язково», поле «Поточне значення» не може бути пустим. Щоб створити параметр, натисніть кнопку OK.
Використання параметра для змінення джерела даних
У цій статті описано, як керувати змінами в розташуваннях джерел даних і запобігати помилкам оновлення. Наприклад, використовуючи схожі схему та джерело даних, створіть параметр, щоб легко змінити джерело даних і запобігти помилкам оновлення даних. Іноді змінюється сервер, база даних, папка, ім'я файлу або розташування. Наприклад, менеджер баз даних час від часу змінює сервер, щомісячна кількість CSV-файлів переходить в іншу папку або вам потрібно легко перемикатися між середовищем розробки/тестування/виробництва.
Крок 1. Створення параметризованого запиту
У наведеному нижче прикладі є кілька CSV-файлів, які імпортуються за допомогою операції імпорту папки (Select Data>Get Data>From FilesFilesFrom>Folder) з папки C:\DataFilesCSV1. Але іноді для скидання файлів іноді використовується інша папка: C:\DataFilesCSV2. Параметр запиту можна використовувати як значення заміни для іншої папки.
Виберіть Головна>Керування параметрами>Новий параметр.
У діалоговому вікні « Керування параметрами » введіть такі відомості:
Ім’я CSVFileDrop Опис Альтернативне розташування для скидання файлу Обов'язковий Так Тип Text (Текст) Рекомендовані значення Будь-яке значення Поточне значення C:\DataFilesCSV1 Натисніть кнопку OK.
Крок 2. Додавання параметра до запиту даних
- Щоб установити ім'я папки як параметр, у розділі " Параметри запиту" виберіть " Джерело", а потім – " Змінити параметри".
- Переконайтеся, що для параметра "Шлях до файлу " встановлено значення "Параметр", а потім виберіть щойно створений параметр із розкривного списку.
- Натисніть кнопку OK.
Крок 3. Оновіть значення параметра
Щойно розташування папки змінилося, тому тепер можна просто оновити параметризований запит.
- Виберіть"Зв'язки даних>" & вкладці"Запити>", клацніть правою кнопкою миші параметризований запит, а потім виберіть команду "Редагувати".
- Введіть нове розташування в полі поточного значення , наприклад C:\DataFilesCSV2.
- Виберіть Головна>сторінка Закрити & Завантажити.
- Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит на дані з оновленим параметром (виберіть команду>"Оновити все").
Використання параметра для фільтрування даних
Іноді потрібно легко змінити фільтр запиту, щоб отримати інші результати, не редагуючи запит і не створюючи дещо інших копій того самого запиту. У цьому прикладі ми змінюємо дату, щоб було зручно змінити фільтр даних.
Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Клацніть стрілку фільтра в заголовку будь-якого стовпця, щоб відфільтрувати дані, а потім виберіть команду фільтрування, наприклад "Фільтри дати й часу"Фільтри> після". Відкриється діалогове вікно "Фільтр рядків ".
Натисніть кнопку ліворуч від поля "Значення " та виконайте одну з таких дій:
- Щоб використати наявний параметр, виберіть пункт Параметр, а потім виберіть потрібний параметр зі списку, що з'явиться праворуч.
- Щоб використовувати новий параметр, виберіть пункт "Створити параметр", а потім створіть параметр.
Введіть нову дату в поле "Поточне значення " та виберіть пункт "Закрити &>завантажити".
Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит на дані з оновленим параметром (виберіть команду>"Оновити все"). Наприклад, змініть дату фільтра, щоб побачити нові результати.
Введіть нову дату в поле "Поточне значення ".
Виберіть Головна>сторінка Закрити & Завантажити.
Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит на дані з оновленим параметром (виберіть команду>"Оновити все").
Використання значення клітинки для фільтрування даних
У цьому прикладі значення в параметрі запиту читається з клітинки книги. Не потрібно змінювати параметризований запит, достатньо оновити значення клітинки. Наприклад, потрібно відфільтрувати стовпець за першою буквою, але легко змінити значення на будь-яку букву від А до Я.
На аркуші книги, де завантажено запит, який потрібно відфільтрувати, створіть таблицю Excel із двома клітинками: заголовком і значенням.
MyFilter G Виділіть клітинку в таблиці Excel, а потім виберіть пункт«Дані>>з таблиці/діапазону». З'явиться Редактор Power Query.
В області "Параметри запиту" в полі "Ім'я" праворуч змініть ім'я запиту на змістовніше, наприклад "Фільтрзначенняклітинки".
Щоб передати значення з таблиці, а не саму таблицю, клацніть його правою кнопкою миші в режимі попереднього перегляду даних і виберіть команду " Деталізувати".
Зверніть увагу, що формула змінилася на= #"Changed Type"{0}[MyFilter]
Якщо використати таблицю Excel як фільтр на кроці 10, Power Query посилатиметься на значення таблиці як на умову фільтра. Пряме посилання на таблицю Excel призведе до помилки.Виберіть "Домашня сторінка>", "Закрити" & "Завантажити>","Закрити" & "Завантажити до". Тепер у вас є параметр запиту з ім'ям "FilterCellValue", який використовується на кроці 12.
У діалоговому вікні "Імпорт даних" виберіть параметр "Лише створити підключення", потім натисніть кнопку "OK".
Відкрийте запит, який потрібно відфільтрувати зі значенням із таблиці FilterCellValue, попередньо завантаженої з Редактор Power Query, вибравши клітинку в даних і натиснувши кнопку "Редагувати>" запиту. Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Клацніть стрілку фільтра в заголовку будь-якого стовпця, щоб відфільтрувати дані, а потім виберіть команду фільтрування, наприклад "Текстові фільтри>починаються з". Відкриється діалогове вікно "Фільтр рядків ".
У поле "Значення" введіть будь-яке значення, наприклад "G", і натисніть кнопку "OK". У такому разі значення буде тимчасовим покажчиком місця заповнення для значення з таблиці FilterCellValue, яку ви введете на наступному кроці.
Клацніть стрілку праворуч у рядку формул, щоб відобразити всю формулу. Ось приклад умови фільтрування у формулі:
= Table.SelectRows(#"Змінений тип", each Text.StartsWith([Name], "G"))
Виберіть значення фільтра. У формулі вибираємо «Г».
Введіть за допомогою технології M Intellisense перші букви створеної таблиці FilterCellValue, а потім виберіть її зі списку, що з'явиться.
Виберіть "Домашня сторінка", "Закрити>>","Закрити" & "Завантажити".
Результат
Тепер у запиті використовуватиметься значення з таблиці Excel, створеної для фільтрування результатів запиту. Щоб використати нове значення, змініть вміст клітинки у вихідній таблиці Excel на кроці 1, змініть літеру "G" на "V", а потім оновіть запит.
Керування використанням параметризованих запитів
Параметризовані запити можна дозволити або заборонити.
- In the Power Query EditorРедактор Power Query, select File>Options and Settings>Query OptionsPower>Query EditorРедактор Power Query.
- В області ліворуч у розділі GLOBAL (глобально) виберіть Редактор Power Query EditorРедактор Power Query.
- В області праворуч у розділі «Параметри» встановіть або зніміть прапорець «Завжди дозволяти параметризацію в діалогових вікнах джерела даних і перетворення».
Додаткові відомості
Power QueryДовідка з надбудови Power Query для програми Excel