Возможно, вы хорошо знакомы с запросами параметров, которые используются в SQL или Microsoft Query. Однако Power Query параметры имеют ключевые отличия:
-
Параметры можно использовать на любом шаге запроса. В дополнение к работе в качестве фильтра данных параметры можно использовать для указания таких вещей, как путь к файлу или имя сервера.
-
Параметры не запрашивают входные данные. Вместо этого можно быстро изменить их значение с помощью Power Query. Вы даже можете сохранять и извлекать значения из ячеек в Excel.
-
Параметры сохраняются в простом запросе параметров, но отличаются от запросов данных, в которых они используются. После создания можно добавить параметр в запросы по мере необходимости.
Примечание Если вам нужен другой способ создания запросов параметров, см. статью Создание запроса параметров в Microsoft Query.
Параметр можно использовать для автоматического изменения значения в запросе и не изменять его каждый раз, чтобы изменить значение. Вы просто измените значение параметра. После создания параметра он сохраняется в специальном запросе параметров, который можно легко изменить непосредственно из Excel.
-
Выберите Данные > Получить данные > другие источники > запустить Редактор Power Query.
-
В Редактор Power Query выберите Главная > Управление параметрами > Новые параметры.
-
В диалоговом окне Управление параметром выберите Создать.
-
При необходимости задайте следующие параметры:
Имя
Это должно отражать функцию параметра, но держать ее как можно короче.
Описание
Он может содержать любые сведения, которые помогут пользователям правильно использовать параметр.
Обязательные
Выполните одно из следующих действий:
Любое значение. В запросе к параметру можно ввести любое значение любого типа данных. Список значений Значения можно ограничить определенным списком, введя их в небольшую сетку. Также необходимо выбрать значения по умолчанию и Текущее значение ниже. Запроса Выберите запрос списка, который похож на структурированный столбец списка , разделенный запятыми и заключенный в фигурные скобки. Например, поле состояния проблем может содержать три значения: {"Новый", "Текущий", "Закрытый"}. Необходимо создать запрос списка заранее, открыв Расширенный редактор (выберите Главная > Расширенный редактор), удалив шаблон кода, введя список значений в формате списка запросов и выбрав Готово. После создания параметра запрос списка отобразится в значениях параметров.Тип
Это указывает тип данных параметра .
Предлагаемые значения
При необходимости добавьте список значений или укажите запрос для предоставления предложений для входных данных.
Значение по умолчанию
Оно отображается только в том случае, если для параметра Предлагаемые значения задано значение Список значений, и указывает, какой элемент списка используется по умолчанию. В этом случае необходимо выбрать значение по умолчанию.
Текущее значение
В зависимости от того, где используется параметр, если этот параметр пуст, запрос может не возвращать результатов. Если выбран параметр Обязательно , текущее значение не может быть пустым.
-
Чтобы создать параметр, нажмите кнопку ОК.
Вот способ управления изменениями в расположениях источников данных и предотвращения ошибок обновления. Например, предполагая аналогичную схему и источник данных, создайте параметр, чтобы легко изменять источник данных и предотвращать ошибки обновления данных. Иногда изменяется сервер, база данных, папка, имя файла или расположение. Возможно, диспетчер баз данных иногда переключает сервер, ежемесячное удаление CSV-файлов попадает в другую папку или вам нужно легко переключаться между средой разработки, тестирования и рабочей среды.
Шаг 1. Создание запроса параметров
В следующем примере у вас есть несколько CSV-файлов, импортируемых с помощью операции импорта папки (выбор данных > Получение данных > из файлов > из папки) из папки C:\DataFilesCSV1. Но иногда в качестве расположения для удаления файлов иногда используется другая папка C:\DataFilesCSV2. Параметр в запросе можно использовать в качестве замены значения для другой папки.
-
Выберите Главная > Управление параметрами > Новый параметр.
-
В диалоговом окне Управление параметром введите следующие сведения:
Имя
CSVFileDrop
Описание
Альтернативное расположение удаления файла
Обязательные
Да
Тип
Текст
Предлагаемые значения
Любое значение
Текущее значение
C:\DataFilesCSV1
-
Нажмите кнопку ОК.
Шаг 2. Добавление параметра в запрос данных
-
Чтобы задать имя папки в качестве параметра, в разделе Параметры запроса в разделе Шаги запроса выберите Источник, а затем — Изменить параметры.
-
Убедитесь, что для параметра Путь к файлузадано значение Параметр, а затем выберите только что созданный параметр в раскрывающемся списке.
-
Нажмите кнопку ОК.
Шаг 3. Обновление значения параметра
Расположение папки только что изменилось, поэтому теперь можно просто обновить запрос параметров.
-
Выберите Подключения> данных & запросы > вкладку Запросы , щелкните правой кнопкой мыши запрос параметров и выберите изменить.
-
Введите новое расположение в поле Текущее значение , например C:\DataFilesCSV2.
-
Выберите Главная > Закрыть & Загрузить.
-
Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все).
Иногда требуется простой способ изменить фильтр запроса, чтобы получить различные результаты, не изменяя запрос или не делая несколько разных копий одного и того же запроса. В этом примере мы изменяем дату, чтобы удобно изменить фильтр данных.
-
Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
-
Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например Фильтры даты и времени > After. Откроется диалоговое окно Фильтрация строк .
-
Нажмите кнопку слева от поля Значение и выполните одно из следующих действий:
-
Чтобы использовать существующий параметр, выберите Параметр, а затем выберите нужный параметр в списке справа.
-
Чтобы использовать новый параметр, выберите Создать параметр и создайте параметр.
-
-
Введите новую дату в поле Текущее значение , а затем выберите Главная > Закрыть & загрузить.
-
Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все). Например, измените значение фильтра на другую дату, чтобы увидеть новые результаты.
-
Введите новую дату в поле Текущее значение .
-
Выберите Главная > Закрыть & Загрузить.
-
Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все).
В этом примере значение в параметре запроса считывается из ячейки в книге. Вам не нужно изменять запрос параметров, просто обновите значение ячейки. Например, вы хотите отфильтровать столбец по первой букве, но легко изменить значение на любую букву с A на Я.
-
На листе книги, где загружается запрос, который требуется отфильтровать, создайте таблицу Excel с двумя ячейками: заголовком и значением.
MyFilter
G
-
Выберите ячейку в таблице Excel, а затем выберите Данные > Получить данные > из таблицы или диапазона. Появится Редактор Power Query.
-
В поле Имя области Параметры запроса справа измените имя запроса на более понятное, например FilterCellValue.
-
Чтобы передать значение в таблице, а не саму таблицу, щелкните его правой кнопкой мыши в режиме предварительного просмотра данных и выберите пункт Детализация.
Обратите внимание, что формула изменена на = #"Changed Type"{0}[MyFilter]
При использовании таблицы Excel в качестве фильтра на шаге 10 Power Query ссылается на значение Таблицы в качестве условия фильтра. Прямая ссылка на таблицу Excel приведет к ошибке.
-
Выберите Главная > Закрыть & Загрузить > Закрыть & Загрузить. Теперь у вас есть параметр запроса с именем FilterCellValue, который вы используете на шаге 12.
-
В диалоговом окне Импорт данных выберите Только создать подключение, а затем нажмите кнопку ОК.
-
Откройте запрос, который требуется отфильтровать с помощью значения в таблице FilterCellValue, которая была загружена ранее из Редактор Power Query, выбрав ячейку в данных, а затем выберите Запрос > Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
-
Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например Текстовые фильтры > начинается с. Откроется диалоговое окно Фильтрация строк .
-
Введите любое значение в поле Значение , например "G", а затем нажмите кнопку ОК. В этом случае значение является временным заполнителем значения в таблице 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.
-
В области справа в разделе Параметры выберите или снимите флажок Всегда разрешать параметризацию в диалоговых окнах источника данных и преобразования.