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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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. Выберите Главная > Закрыть > Закрыть & загрузить.

Результат

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

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

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

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

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