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