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

Параметр можно использовать для автоматического изменения значения в запросе и не изменять его каждый раз, чтобы изменить значение. Вы просто измените значение параметра. После создания параметра он сохраняется в специальном запросе параметров, который можно легко изменить непосредственно в Excel.

1. Выберите Данные > Получить данные > другие источники > запустить Редактор Power Query.

2. В Редактор Power Query выберите Главная > Управление параметрами > Новые параметры.

3. В диалоговом окне Управление параметром выберите Создать.

4. При необходимости задайте следующие параметры:

   Name (Имя)	Это должно отражать функцию параметра, но держать ее как можно короче.
   Описание	Он может содержать любые сведения, которые помогут пользователям правильно использовать параметр.
   Обязательный	Выполните одно из следующих действий: Любое значение. В запросе к параметру можно ввести любое значение любого типа данных. Список значений Значения можно ограничить определенным списком, введя их в небольшую сетку. Также необходимо выбрать значения по умолчанию и Текущее значение ниже. Запрос Выберите запрос списка, который похож на структурированный столбец списка , разделенный запятыми и заключенный в фигурные скобки. Например, поле состояния проблем может содержать три значения: {"Новый", "Текущий", "Закрытый"}. Необходимо создать запрос списка заранее, открыв Расширенный редактор (выберите Главная > Расширенный редактор), удалив шаблон кода, введя список значений в формате списка запросов и выбрав Готово. После создания параметра запрос списка отобразится в значениях параметров.
   Type (Тип)	Это указывает тип данных параметра .
   Предлагаемые значения	При необходимости добавьте список значений или укажите запрос для предоставления предложений для входных данных.
   Значение по умолчанию	Оно отображается только в том случае, если для параметра Предлагаемые значения задано значение Список значений, и указывает, какой элемент списка используется по умолчанию. В этом случае необходимо выбрать значение по умолчанию.
   Текущее значение	В зависимости от того, где используется параметр, если он пуст, запрос может не возвращать результатов. Если выбран параметр Обязательно , текущее значение не может быть пустым.

5. Чтобы создать параметр, нажмите кнопку ОК.

Вот способ управления изменениями в расположениях источников данных и предотвращения ошибок обновления. Например, предполагая аналогичную схему и источник данных, создайте параметр, чтобы легко изменять источник данных и предотвращать ошибки обновления данных. Иногда изменяется сервер, база данных, папка, имя файла или расположение. Возможно, диспетчер баз данных иногда переключает сервер, ежемесячное удаление CSV-файлов попадает в другую папку или вам нужно легко переключаться между средой разработки, тестирования и рабочей среды.

Шаг 1. Создание запроса параметров

В следующем примере у вас есть несколько CSV-файлов, импортируемых с помощью операции импорта папки (выбор данных > Получение данных > из файлов > из папки) из папки C:\DataFilesCSV1. Но иногда в качестве расположения для удаления файлов иногда используется другая папка C:\DataFilesCSV2. Параметр в запросе можно использовать в качестве замены значения для другой папки.

1. Выберите Главная > Управление параметрами > Новый параметр.

2. В диалоговом окне Управление параметром введите следующие сведения:

   Name (Имя)	CSVFileDrop
   Описание	Альтернативное расположение удаления файла
   Обязательный	Да
   Type (Тип)	Text (Текст)
   Предлагаемые значения	Любое значение
   Текущее значение	C:\DataFilesCSV1

3. Нажмите кнопку ОК.

Шаг 2. Добавление параметра в запрос данных

1. Чтобы задать имя папки в качестве параметра, в разделе Параметры запроса в разделе Шаги запроса выберите Источник, а затем — Изменить параметры.

2. Убедитесь, что для параметра Путь к файлузадано значение Параметр, а затем выберите только что созданный параметр в раскрывающемся списке.

3. Нажмите кнопку ОК.

Шаг 3. Обновление значения параметра

Расположение папки только что изменилось, поэтому теперь можно просто обновить запрос параметров.

1. Выберите > данных Connections & вкладку Запросы > Запросы, щелкните правой кнопкой мыши запрос параметров и выберите изменить.

2. Введите новое расположение в поле Текущее значение , например C:\DataFilesCSV2.

3. Выберите Главная > Закрыть & Загрузить.

4. Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все).

Иногда требуется простой способ изменить фильтр запроса, чтобы получить различные результаты, не изменяя запрос или не делая несколько разных копий одного и того же запроса. В этом примере мы изменяем дату, чтобы удобно изменить фильтр данных.

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

2. Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например Фильтры даты и времени > After. Откроется диалоговое окно Фильтрация строк .
   
   

3. Нажмите кнопку слева от поля Значение и выполните одно из следующих действий:

   • Чтобы использовать существующий параметр, выберите Параметр, а затем выберите нужный параметр в списке справа.

   • Чтобы использовать новый параметр, выберите Создать параметр и создайте параметр.

4. Введите новую дату в поле Текущее значение , а затем выберите Главная > Закрыть & загрузить.

5. Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все). Например, измените значение фильтра на другую дату, чтобы увидеть новые результаты.

6. Введите новую дату в поле Текущее значение .

7. Выберите Главная > Закрыть & Загрузить.

8. Чтобы подтвердить результаты, добавьте новые данные в источник данных, а затем обновите запрос данных с обновленным параметром (Выберите данные > Обновить все).

В этом примере значение в параметре запроса считывается из ячейки в книге. Вам не нужно изменять запрос параметров, просто обновите значение ячейки. Например, вы хотите отфильтровать столбец по первой букве, но легко изменить значение на любую букву с A на Я.

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

   MyFilter
   G

2. Выберите ячейку в таблице Excel, а затем выберите Данные > Получить данные > из таблицы или диапазона. Появится Редактор Power Query.

3. В поле Имя области Параметры запроса справа измените имя запроса на более понятное, например FilterCellValue.

4. Чтобы передать значение в таблице, а не саму таблицу, щелкните его правой кнопкой мыши в режиме предварительного просмотра данных и выберите пункт Детализация.

   Обратите внимание, что формула изменена на = #"Changed Type"{0}[MyFilter]

   При использовании таблицы Excel в качестве фильтра на шаге 10 Power Query ссылается на значение Таблицы в качестве условия фильтра. Прямая ссылка на таблицу Excel приведет к ошибке.

5. Выберите Главная > Закрыть & Загрузить > Закрыть & Загрузить. Теперь у вас есть параметр запроса с именем FilterCellValue, который вы используете на шаге 12.

6. В диалоговом окне Импорт данных выберите Только создать подключение, а затем нажмите кнопку ОК.

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

8. Щелкните стрелку фильтра в заголовке любого столбца, чтобы отфильтровать данные, а затем выберите команду фильтра, например Текстовые фильтры > начинается с. Откроется диалоговое окно Фильтрация строк .

9. Введите любое значение в поле Значение , например "G", а затем нажмите кнопку ОК. В этом случае значение является временным заполнителем значения в таблице FilterCellValue, которое вы вводите на следующем шаге.

10. Щелкните стрелку в правой части строки формул, чтобы отобразить всю формулу. Ниже приведен пример условия фильтра в формуле:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

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

12. С помощью M Intellisense введите первые несколько букв созданной таблицы FilterCellValue, а затем выберите ее в появившемся списке.

13. Выберите Главная > Закрыть > Закрыть & загрузить.

Result (Результат)

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

Вы можете указать, разрешены ли запросы параметров.

1. В Редактор Power Query выберите Параметры> файлов и Параметры > Параметры запроса > Редактор Power Query.

2. В области слева в разделе Глобальный выберите Редактор Power Query.

3. В области справа в разделе Параметры выберите или снимите флажок Всегда разрешать параметризацию в диалоговых окнах источника данных и преобразования.