Можливо, ви добре знайомі з параметрами запитів, які використовуються в SQL або Microsoft Query. Проте Power Query параметри мають основні відмінності:
- Параметри можна використовувати на будь-якому кроці запиту. Окрім фільтра даних, за допомогою параметрів можна вказати такі параметри, як шлях до файлу або ім'я сервера.
- Параметри не відображають запит на введення. Натомість можна швидко змінити значення за допомогою Power Query. У програмі Excel можна навіть зберігати та отримувати значення з клітинок.
- Параметри зберігаються в простому параметризованій запиті, але відокремлені від запитів даних, у яких вони використовуються. Створений параметр можна додати до запитів за потреби.
Примітка Якщо вам потрібен інший спосіб створення параметризаторних запитів, див. статтю Створення параметризаторних запитів у Microsoft Query.
Створення параметра
Параметр дає змогу автоматично змінювати значення в запиті та не змінювати запит щоразу, щоб змінювати значення. Ви просто змінюєте значення параметра. Створений параметр зберігається в спеціальному параметризований запит, який можна зручно змінити безпосередньо з Програми Excel.
Виберіть параметр> Запуск іншихджерел>даних отримати дані>Редактор Power Query.
У Редактор Power Query виберіть елемент HomeManage Parameters > New Parameters (Параметри home > Manage New Parameters).
У діалоговому вікні Керування параметрами натисніть кнопку Створити.
За потреби виконайте наведені нижче дії.
Ім’я Це має відображати функцію параметра, але залишити його якомога коротшим. Опис Це може містити будь-які відомості, які допоможуть користувачам правильно використовувати цей параметр. Необхідні Виконайте одну з таких дій:
Будь-яке значення У параметризований запит можна ввести будь-яке значення будь-якого типу даних.
Список значень Ви можете обмежити значення певним списком, ввівши їх у маленьку сітку. Також потрібно вибрати значення за промовчанням і поточне значення нижче.
Запит Виберіть запит списку, який нагадує структурований стовпець списку , розділений комами та взятий у фігурні дужки.
Наприклад, поле стану питань може мати три значення: {"Створити", "Триває", "Закрито"}. Створіть запит списку заздалегідь, відкривши Розширений редактор (виберіть Основне>Розширений редактор), видаливши шаблон коду, ввівши список значень у форматі списку запитів і натиснувши кнопку Готово.
Коли ви завершите створення параметра, запит списку відображатиметься у значеннях параметрів.Тип Цей параметр визначає тип даних. Рекомендовані значення За потреби додайте список значень або вкажіть запит для надання пропозицій для вводу. Значення за промовчанням Цей параметр відображається, лише якщо для параметра Suggested Values (Запропоновані значення ) установлено значення List of values (Список значень) і вказано, який елемент списку використовуватиметься за замовчуванням. У такому разі потрібно вибрати стандартне значення. Поточне значення Залежно від того, де використовується параметр, якщо цей параметр пустий, запит може не повертати результатів. Якщо вибрано параметр Обов'язковий , поточне значення не може бути пустим. Щоб створити параметр, натисніть кнопку OK.
Змінення джерела даних за допомогою параметра
Ось як можна керувати змінами в розташуваннях джерел даних і запобігти помилкам оновлення. Наприклад, за умови подібної схеми та джерела даних створіть параметр, щоб легко змінити джерело даних і запобігти помилкам оновлення даних. Іноді змінюється сервер, база даних, папка, ім'я файлу або розташування. Можливо, диспетчер баз даних час від часу міняє сервер, щомісячне падіння CSV-файлів передається в іншу папку або вам потрібно легко переключатися між середовищем розробки, тестування/ виробництва.
Крок 1. Створення параметризований запит
У наведеному нижче прикладі є кілька CSV-файлів, які імпортуються за допомогою операції імпорту папки (Select Data>Get Data>From Files>From Folder) з папки C:\DataFilesCSV1. Але іноді інша папка іноді використовується як розташування для видалення файлів C:\DataFilesCSV2. Параметр у запиті можна використовувати як значення для заміни іншої папки.
Виберіть Елемент HomeManage ParametersNew Parameter (Створити параметр home >Manage Parameters>).
Введіть такі відомості в діалоговому вікні Керування параметрами :
Ім’я CsvFileDrop Опис Альтернативне розташування для перетягування файлів Необхідні Так Тип Text (Текст) Рекомендовані значення Будь-яке значення Поточне значення C:\DataFilesCSV1 Натисніть кнопку OK.
Крок 2. Додавання параметра до запиту даних
- Щоб установити ім'я папки як параметр, у розділі Параметри запиту в розділі Кроки запиту виберіть пункт Джерело, а потім – Редагувати настройки.
- Переконайтеся, що для параметра Шлях до файлу встановлено значення Параметр, а потім виберіть щойно створений параметр із розкривного списку.
- Натисніть кнопку OK.
Крок 3. Оновлення значення параметра
Розташування папки щойно змінено, тому тепер можна просто оновити параметризований запит.
- ВиберітьЗв'язкиданих> & вкладціЗапити> запити, клацніть правою кнопкою миші параметризований запит, а потім виберіть Редагувати.
- Введіть нове розташування в полі Поточне значення , наприклад C:\DataFilesCSV2.
- Натисніть кнопку Основне>Закрити & Завантажити.
- Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит даних за допомогою оновленого параметра (Виберіть пункт"Оновити всідані>").
Використання параметра для фільтрування даних
Інколи потрібно легко змінити фільтр запиту, щоб отримати різні результати, не редагуючи запит або не роблячи дещо інші копії одного запиту. У цьому прикладі ми змінюємо дату, щоб зручно змінювати фільтр даних.
Щоб відкрити запит, знайдіть попередньо завантажену з Редактор Power Query, виділіть клітинку в даних і натисніть кнопкуРедагуватизапит>. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.
Клацніть стрілку фільтра в заголовку будь-якого стовпця, щоб відфільтрувати дані, а потім виберіть команду фільтра, наприклад "Фільтри> дати й часупісля". Відкриється діалогове вікно Фільтрування рядків .
Натисніть кнопку ліворуч від поля Значення , а потім виконайте одну з таких дій:
- Щоб використати наявний параметр, виберіть Параметр, а потім виберіть потрібний параметр у списку, який з'явиться праворуч.
- Щоб використати новий параметр, натисніть кнопку Створити параметр, а потім створіть параметр.
Введіть нову дату в полі Поточне значення , а потім натисніть кнопку Закрити основне>& Завантажити.
Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит даних за допомогою оновленого параметра (Виберіть пункт"Оновити всідані>"). Наприклад, змініть значення фільтра на іншу дату, щоб переглянути нові результати.
Введіть нову дату в полі Поточне значення .
Натисніть кнопку Основне>Закрити & Завантажити.
Щоб підтвердити результати, додайте нові дані до джерела даних, а потім оновіть запит даних за допомогою оновленого параметра (Виберіть пункт"Оновити всідані>").
Використання значення клітинки для фільтрування даних
У цьому прикладі значення параметра запиту читається з клітинки в книзі. Не потрібно змінювати параметризований запит, просто оновіть значення клітинки. Наприклад, потрібно відфільтрувати стовпець за першою буквою, але легко змінити значення на будь-яку букву від А до Я.
На аркуші книги, де завантажується запит, який потрібно відфільтрувати, створіть таблицю Excel із двома клітинками: заголовком і значенням.
Мій фільтр G Виділіть клітинку в таблиці Excel, а потім виберіть пункт Дані>Отримати дані>з таблиці або діапазону. З'явиться Редактор Power Query.
У полі Ім'я в області Параметри запиту праворуч змініть ім'я запиту на зрозуміліше, наприклад FilterCellValue.
Щоб передати значення в таблиці, а не саму таблицю, клацніть правою кнопкою миші значення в поданні попереднього перегляду даних і виберіть пункт Деталізувати.
Зверніть увагу, що формулу змінено на= #"Changed Type"{0}[MyFilter]
Коли таблиця Excel використовується як фільтр на кроці 10, Power Query посилається на значення Таблиці як умову фільтра. Пряме посилання на таблицю Excel спричинить помилку.Натисніть кнопку Закрити основне>& Завантажити>закрити & Завантажити до. Тепер у вас є параметр запиту з іменем "FilterCellValue", який використовується на кроці 12.
У діалоговому вікні Імпорт даних виберіть лише створити підключення, а потім натисніть кнопку OK.
Відкрийте запит, який потрібно відфільтрувати зі значенням у таблиці FilterCellValue, який попередньо завантажено з Редактор Power Query, вибравши клітинку в даних і вибравши пунктРедагуватизапит>. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.
Клацніть стрілку фільтра в заголовку будь-якого стовпця, щоб відфільтрувати дані, а потім виберіть команду фільтра, як-от Текстові фільтри>починається з. Відкриється діалогове вікно Фільтрування рядків .
Введіть будь-яке значення в полі Значення , наприклад "G", а потім натисніть кнопку OK. У цьому випадку значення є тимчасовим покажчиком місця заповнення для значення в таблиці FilterCellValue, введеного на наступному кроці.
Клацніть стрілку праворуч від рядка формул, щоб відобразити всю формулу. Ось приклад умови фільтрування у формулі:
= Table.SelectRows(#"Змінений тип", кожен текст.StartsWith([Ім'я], "G"))
Виберіть значення фільтра. У формулі виберіть "G".
За допомогою функції M Intellisense введіть кілька перших букв створеної таблиці FilterCellValue, а потім виберіть її зі списку, що з'явиться.
Натисніть кнопку Закрити >головну сторінку>, & Завантажити.
Результат
Тепер запит використовує значення в таблиці Excel, створеній для фільтрування результатів запиту. Щоб використати нове значення, відредагуйте вміст клітинки у вихідній таблиці Excel на кроці 1, змініть "G" на "V", а потім оновіть запит.
Керування використанням параметрівних запитів
Можна вказати, чи дозволено параметризовані запити.
- У Редактор Power Query виберіть Параметри файлу> таПараметри запитів>параметри>Редактор Power Query.
- В області ліворуч у розділі ГЛОБАЛЬНИЙ виберіть Редактор Power Query.
- В області праворуч у розділі Параметри встановіть або зніміть прапорець Завжди дозволяти параметризування в діалогових вікнах джерела даних і перетворення.