Возможно, вы хорошо знакомы с запросами с параметрами и их использованием в SQL или Microsoft Query. Однако параметры Power Query имеют ключевые отличия:
- Параметры можно использовать на любом шаге запроса. Помимо работы в качестве фильтра данных, параметры могут использоваться для указания таких вещей, как путь к файлу или имя сервера.
- Параметры не запрашивают ввод. Вместо этого вы можете быстро изменить их значение с помощью Power Query. Значения из ячеек можно даже хранить и извлекать из них в Excel.
- Параметры сохраняются в простом запросе с параметрами, но отделены от запросов данных, в которых они используются. После создания вы можете добавлять параметр в запросы по мере необходимости.
Примечание Если вы хотите создавать запросы с параметрами другим способом, см . статью Создание запроса с параметрами в Microsoft Query.
Создание параметра
Параметр можно использовать для автоматического изменения значения в запросе, не изменяя запрос каждый раз. Вы просто меняете значение параметра. После создания параметра он сохраняется в специальном запросе параметров, который удобно изменять непосредственно в Excel.
Выбор данных>Получение данных>Другие источники>Запустите редактор Power Query.
В редакторе Power Query выберите "Главная>страница управления параметрами", "Новые > параметры".
В диалоговом окне "Управление параметрами " нажмите кнопку "Создать".
При необходимости настройте следующие параметры:
Имя Он должен отражать функцию параметра, но должен быть как можно короче. Описание Здесь могут содержаться любые детали, которые помогут людям правильно использовать параметр. Обязательно Выполните одно из следующих действий:
Любое значение В запрос с параметрами можно ввести любое значение любого типа данных.
Список ценностей Можно ограничить значения определенным списком, введя их в небольшую сетку. Необходимо также выбрать значение по умолчанию и текущее значение ниже.
Запрос Выберите запрос списка, похожий на структурированный столбец списка , разделенный запятыми и заключенный в фигурные скобки.
Например, поле состояния проблемы может иметь три значения: {"Новая", "Текущая", "Закрытая"}. Запрос-список необходимо создать заранее, открыв Расширенный редактор (выберите "Главная>Расширенный редактор), удалив шаблон кода, введя список значений в формате списка запроса и нажав кнопку "Готово".
После завершения создания параметра в значениях параметров отображается запрос к списку.Тип Определяет тип данных параметра. Предлагаемые значения При необходимости добавьте список значений или укажите запрос для предоставления предложений для ввода. Значение по умолчанию Эта область отображается, только если для параметра "Рекомендуемые значения " задано значение " Список значений" и указывается, какой элемент списка используется по умолчанию. В этом случае необходимо выбрать значение по умолчанию. Текущее значение В зависимости от того, где используется параметр, если он пуст, запрос может не вернуть результатов. Если выбран параметр "Обязательно ", текущее значение не может быть пустым. Чтобы создать параметр, нажмите кнопку "ОК".
Изменение источника данных с помощью параметра
Это способ управлять изменениями расположений источников данных и предотвращать ошибки обновления. Например, предполагая схожую схему и источник данных, создайте параметр, который легко изменит источник данных и поможет предотвратить ошибки обновления данных. Иногда изменяется сервер, база данных, папка, имя файла или расположение. Возможно, менеджер баз данных время от времени меняет сервер, ежемесячное удаление CSV-файлов попадает в другую папку или вам нужно легко переключаться между средой разработки, тестирования и рабочей средой.
Шаг 1. Создание запроса с параметрами
В приведенном ниже примере у вас есть несколько CSV-файлов, которые вы импортируете с помощью операции импорта папок (Выбрать данные>,получить данные> из папки FilesFrom>) из папки C:\DataFilesCSV1. Но иногда в качестве расположения для хранения файлов используется другая папка: C:\DataFilesCSV2. В запросе можно использовать параметр в качестве подстановочного значения для другой папки.
Выберите "Главная>: управление параметрами>,новый параметр".
В диалоговом окне "Управление параметрами " введите следующие сведения.
Имя CSVFileDrop Описание Альтернативное место хранения файлов Обязательно Да Тип Text (Текст) Предлагаемые значения Любое значение Текущее значение C:\DataFilesCSV1 Нажмите кнопку ОК.
Шаг 2. Добавление параметра в запрос данных
- Чтобы задать имя папки в качестве параметра, в параметрах запроса в разделе "Этапы запроса" выберите "Источник", а затем " Изменить параметры".
- Убедитесь, что параметру "Путь к файлу " присвоено значение "Параметр", а затем выберите только что созданный параметр из раскрывающегося списка.
- Нажмите кнопку ОК.
Шаг 3. Обновление значения параметра
Расположение папки только что изменилось, поэтому теперь можно просто обновить запрос с параметрами.
- Выберите "Подключения кданным>" & вкладке"Запросы>", щелкните правой кнопкой мыши запрос с параметрами и выберите команду "Изменить".
- Введите новое расположение в поле "Текущее значение ", например "C:\DataFilesCSV2".
- Выберите "Главная",>"Закрыть& "Загрузить".
- Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных, указав обновленный параметр (Выберите"Обновлениеданных>").
Использование параметра для фильтрации данных
Иногда требуется простой способ изменить фильтр запроса для получения других результатов без редактирования запроса или создания немного других копий одного и того же запроса. В этом примере мы изменяем дату, чтобы удобно изменить фильтр данных.
Чтобы открыть запрос, найдите запрос, ранее загруженный из редактора Power Query, выберите ячейку в данных, а затем выберите "Редактировать запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например "Фильтрация> даты и временипосле". Откроется диалоговое окно фильтрации строк.
Нажмите кнопку слева от поля "Значение " и выполните одно из указанных ниже действий.
- Чтобы использовать существующий параметр, нажмите "Параметр", а затем выберите нужный параметр из списка справа.
- Чтобы использовать новый параметр, выберите Создать параметр и создайте параметр.
Введите новую дату в поле "Текущее значение" и нажмите "Главная>"Закрыть & "Загрузить".
Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных, указав обновленный параметр (Выберите"Обновлениеданных>"). Например, измените значение фильтра на другую дату, чтобы увидеть новые результаты.
Введите новую дату в поле "Текущее значение ".
Выберите "Главная",>"Закрыть& "Загрузить".
Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных, указав обновленный параметр (Выберите"Обновлениеданных>").
Использование значения в ячейке для фильтрации данных
В этом примере значение в параметре запроса считывается из ячейки в книге. Вам не нужно изменять запрос параметров, достаточно обновить значение в ячейке. Например, требуется отфильтровать столбец по первой букве, но при этом легко изменить значение на любую букву от A до Z.
На листе книги, куда загружен запрос, который нужно отфильтровать, создайте таблицу Excel с двумя ячейками: заголовком и значением.
Мой фильтр G Выберите ячейку в таблице Excel, а затем выберите "Данные>"Получить данные>из таблицы или диапазона. Откроется редактор Power Query.
В поле "Имя " панели "Параметры запроса " справа измените имя запроса, чтобы оно было более понятным, например "FilterCellValue".
Чтобы передать значение из таблицы, а не из самой таблицы, в режиме предварительного просмотра данных щелкните его правой кнопкой мыши и выберите команду "Детализация".
Обратите внимание, что формула изменена на= #"Changed Type"{0}[MyFilter]
При использовании таблицы Excel в качестве фильтра на шаге 10 Power Query ссылается на значение таблицы как на условие фильтра. Прямая ссылка на таблицу Excel вызывала ошибку.Выберите Главная>Закрыть & Загрузить>Закрыть & Загрузить в. Теперь у вас есть параметр запроса с именем "FilterCellValue", который вы используете на шаге 12.
В диалоговом окне "Импорт данных" выберите "Только создание подключения" и нажмите кнопку "ОК".
Откройте запрос, который нужно отфильтровать по значению из таблицы FilterCellValue, ранее загруженной из редактора Power Query, выбрав ячейку в данных, а затем нажав "Изменить запрос>". Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например "Текстовые фильтры>начинаются с". Откроется диалоговое окно фильтрации строк.
Введите в поле "Значение" любое значение, например "G", и нажмите кнопку "OK". В этом случае значением является временный заполнитель для значения в таблице FilterCellValue, которое вы введете на следующем шаге.
Щелкните стрелку справа от строки формул, чтобы отобразить всю формулу. Вот пример условия фильтра в формуле:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Выберите значение фильтра. В формуле выберите "G".
С помощью функции M Intellisense введите первые несколько букв созданной таблицы FilterCellValue и выберите ее из появившегося списка.
Выберите "Главная",>"Закрыть>", "Закрыть& "Загрузить".
Результат
Теперь в запросе будет использоваться значение из таблицы Excel, созданной вами для фильтрации результатов запроса. Чтобы использовать новое значение, измените содержимое ячейки в исходной таблице Excel на шаге 1, замените "G" на "V", а затем обновите запрос.
Управление использованием запросов с параметрами
Можно управлять разрешениями запросов с параметрами.
- В редакторе Power Query выберите «Параметры файла>и параметры>>»Редактор Power Query.
- На панели слева в разделе "Глобальные" выберите "Редактор Power Query".
- В области справа в разделе "Параметры" установите или снимите флажок "Всегда разрешать параметризацию в диалоговых окнах источника данных и преобразования".