Управление параметрами запроса мощности в Excel

Обновлено: 20.11.2024

Когда вы создаете запрос Power в Excel для импорта или преобразования данных, Excel создает запрос за кулисами на языке M. Вы можете увидеть этот код M, перейдя в Расширенный редактор из редактора запросов Power. .

При создании кода его элементы будут жестко закодированы. Например, если вы импортируете данные из внешнего источника, путь к папке и имя файла будут жестко заданными (статическими или неизменными) строками текста в M-коде.

Если вы хотите обновить путь к папке или имя файла для импорта, вам нужно перейти в расширенный редактор и обновить путь и имя файла.

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

Видеоруководство

Настройка таблицы параметров

Давайте настроим нашу таблицу параметров. Для этого запроса я хочу импортировать CSV из папки, а затем отфильтровать по заданному продукту и диапазону дат. Я хочу иметь входные параметры, чтобы легко обновлять путь к папке, имя файла, продукт и диапазон дат, на которых будет основан мой запрос.

В таблице, которую я создал, есть 3 столбца, но на самом деле ей нужен только столбец «Значение», в котором находится входное значение. Столбцы «Индекс и параметр» предназначены для информации и просто напоминают мне, в каком номере строки находится значение (индекс), и описание того, для чего используется это значение (параметр).

Нам нужно преобразовать данные параметра в таблицу Excel, перейдя на вкладку «Вставка» и выбрав «Таблица» или воспользовавшись сочетанием клавиш Ctrl + T. Назовите таблицу Параметры, именно так мы будем ссылаться на таблицу в нашем запросе мощности. Чтобы назвать таблицу, выберите ее, перейдите на вкладку «Дизайн» и введите новое имя в поле «Имя таблицы:».

Создайте функцию запроса для ссылки на таблицу параметров

Создайте пустой запрос. Перейдите на вкладку «Данные» на ленте и выберите «Получить данные» в разделе «Получить и преобразовать данные». Выберите «Из других источников», затем выберите «Пустой запрос» в меню.

Назовите запрос fParameters. Именно так вы будете называть значения в таблице параметров.

Откройте расширенный редактор на вкладке "Главная" или на вкладке "Вид" в редакторе запросов. Скопируйте и вставьте следующий код, затем нажмите кнопку "Готово".

Эта функция запроса имеет два входных параметра TableName и RowNumber.

TableName — этот ввод относится к имени таблицы. В нашем случае это будут «Параметры».
RowNumber – этот ввод относится к номеру строки, в которой находится интересующий нас параметр в нашей таблице.

Обратите внимание, что power query будет считать строки с 0, поэтому мы будем использовать RowNumber-1, чтобы ссылаться на наши строки в естественном порядке подсчета от 1 до N.

Теперь сохраните функцию запроса.

Перейдите на вкладку "Главная" в редакторе запросов.
Выберите "Закрыть и загрузить".
Выберите в меню Закрыть и загрузить в.
Выберите «Только создать подключение» в окне «Импорт данных».
Нажмите кнопку "ОК".

Создание исходного запроса импорта и фильтрации

Перейдите на вкладку "Данные" на ленте и выберите "Из текста/CSV" в разделе "Получить и преобразовать данные". Выберите CSV-файл и нажмите кнопку «Изменить» в окне предварительного просмотра результатов запроса.

Теперь добавим фильтр.

Нажмите значок "Фильтр" справа от столбца "Проданный продукт".
Выберите «Текстовый фильтр» в меню.
Выберите в меню "Равно".

Установите фильтр Карандаши и нажмите кнопку ОК.

Теперь мы можем сделать то же самое, чтобы отфильтровать дату заказа между 2017-02-01 и 2017-03-31.

Если вы откроете расширенный редактор на вкладке "Главная", ваш запрос должен выглядеть следующим образом. Вы можете увидеть жестко запрограммированные части (выделены красным ).

Мы можем закрыть и загрузить этот запрос в таблицу на новом листе и просмотреть наши данные.

Замените жестко закодированные элементы нашей функцией запроса

Теперь мы можем заменить любой экземпляр жестко заданной ссылки, которую хотим превратить в параметр в нашем запросе. Замените текст, включая круглые скобки вокруг них, на fParameters("Parameters",N), где N — это индекс в нашей таблице параметров, на который мы хотим ссылаться.

Для дат мне пришлось использовать функцию расширенного запроса Date.Year , Date.Month и Date.Day, чтобы преобразовать даты моих параметров в числа для года, месяца и дня.

Теперь мы можем легко изменить любой из параметров и обновить запрос! С помощью этого метода параметризации мы можем создавать гораздо более гибкие запросы.

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Параметр позволяет легко хранить и управлять значением, которое можно использовать повторно.

Параметры позволяют динамически изменять вывод ваших запросов в зависимости от их значения и могут использоваться для:

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

Вы можете легко управлять своими параметрами в окне "Управление параметрами". Чтобы открыть окно «Управление параметрами», выберите параметр «Управление параметрами» в разделе «Управление параметрами» на вкладке «Главная».

Создание параметра

Power Query предоставляет два простых способа создания параметров:

Из существующего запроса: щелкните правой кнопкой мыши запрос, значение которого представляет собой простую неструктурированную константу, например дату, текст или число, и выберите Преобразовать в параметр.

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

Использование окна «Управление параметрами». Выберите параметр «Новый параметр» в раскрывающемся меню «Управление параметрами» на вкладке «Главная». Или откройте окно «Управление параметрами» и выберите «Создать» вверху, чтобы создать параметр. Заполните эту форму и нажмите кнопку ОК, чтобы создать новый параметр.

После создания параметра вы всегда можете вернуться в окно "Управление параметрами", чтобы в любой момент изменить любой из ваших параметров.

Свойства параметра

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

Описание: описание отображается рядом с именем параметра, когда отображается информация о параметре, помогая пользователям, задающим значение параметра, понять его назначение и семантику.

Обязательно: флажок указывает, могут ли последующие пользователи указать, должно ли быть указано значение параметра.

Тип: определяет тип данных параметра. Мы рекомендуем вам всегда настраивать тип данных вашего параметра. Чтобы узнать больше о важности типов данных, перейдите к разделу Типы данных.

Предлагаемые значения: предлагает пользователю выбрать значение для текущего значения из доступных вариантов:

Любое значение. Текущим значением может быть любое введенное вручную значение.

Список значений. Предоставляет простую таблицу, позволяющую определить список предлагаемых значений, из которого впоследствии можно выбрать текущее значение. При выборе этого параметра становится доступным новый параметр «Значение по умолчанию». Отсюда вы можете выбрать значение по умолчанию для этого параметра, которое является значением по умолчанию, отображаемым пользователю при обращении к параметру. Это значение не совпадает с текущим значением, которое хранится внутри параметра и может быть передано в качестве аргумента в преобразованиях. Использование списка значений предоставляет раскрывающееся меню, которое отображается в полях Значение по умолчанию и Текущее значение, где вы можете выбрать одно из значений из предложенного списка значений.

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

Запрос: использует запрос списка (запрос, результат которого представляет собой список) для предоставления списка предлагаемых значений, которые вы можете позже выбрать для текущего значения.

Текущее значение: значение, сохраненное в этом параметре.

Где использовать параметры

Параметр можно использовать по-разному, но чаще всего он используется в двух случаях:

Аргумент шага. Вы можете использовать параметр в качестве аргумента нескольких преобразований, управляемых из пользовательского интерфейса (UI).
Аргумент пользовательской функции. Вы можете создать новую функцию из запроса и сослаться на параметры в качестве аргументов вашей пользовательской функции.

В следующих разделах вы увидите пример для этих двух сценариев.

Аргумент шага

Чтобы включить эту функцию, сначала перейдите на вкладку "Вид" в редакторе Power Query и выберите параметр "Всегда разрешать" в группе "Параметры".

Например, следующая таблица Orders содержит поля OrderID, Units и Margin.

В этом примере создайте новый параметр с именем «Минимальная маржа», типом «Десятичное число» и текущим значением 0,2.

Перейдите к запросу «Заказы» и в поле «Маржа» выберите параметр фильтра «Больше чем».

В окне «Фильтровать строки» есть кнопка с типом данных для выбранного поля. Выберите параметр «Параметр» в раскрывающемся меню для этой кнопки. В поле выбора рядом с кнопкой типа данных выберите параметр, который вы хотите передать этому аргументу. В данном случае это параметр «Минимальная маржа».

После того, как вы нажмете "ОК", ваша таблица будет отфильтрована с использованием текущего значения параметра.

Если вы измените Текущее значение параметра "Минимальная маржа" на 0,3, запрос заказов немедленно обновится и покажет вам только те строки, в которых маржа превышает 30%.

Многие преобразования в Power Query позволяют выбрать параметр из раскрывающегося списка. Рекомендуем всегда искать его и пользоваться теми параметрами, которые могут вам предложить.

Аргумент пользовательской функции

С помощью Power Query вы можете создать пользовательскую функцию из существующего запроса одним щелчком мыши. Следуя предыдущему примеру, щелкните правой кнопкой мыши запрос «Заказы» и выберите «Создать функцию». Это действие запускает новое окно «Создать функцию».В этом окне назовите вашу новую функцию, и она сообщит вам параметры, на которые ссылается ваш запрос. Эти параметры используются в качестве параметров пользовательской функции.

Вы можете назвать эту новую функцию как хотите. В демонстрационных целях эта новая функция называется MyFunction. После того, как вы нажмете OK, на панели Запросы будет создана новая группа с именем вашей новой функции. В этой группе вы найдете параметры, используемые для функции, запрос, который использовался для создания функции, и саму функцию.

Чтобы протестировать эту новую функцию, введите значение, например 0,4, в поле под меткой "Минимальная маржа". Затем выберите кнопку «Вызвать». Это создает новый запрос с именем Invoked Function, фактически передавая значение 0,4, которое будет использоваться в качестве аргумента для функции, и предоставляя вам только строки, в которых маржа превышает 40%.

Чтобы узнать больше о создании пользовательских функций, перейдите к разделу Создание пользовательской функции.

Многозначные или списковые параметры

Новый тип параметра, доступный только в Power Query Online, — это параметры с несколькими значениями или списком. В этом разделе описывается, как создать новый параметр списка и как использовать его в запросах.

Следуя предыдущему примеру, измените текущее значение минимальной маржи с 0,3 на 0,1. Новая цель — создать параметр списка, который может содержать номера заказов, которые вы хотите проанализировать. Чтобы создать новый параметр, перейдите в диалоговое окно «Управление параметрами» и выберите «Создать», чтобы создать новый параметр. Заполните этот новый параметр следующей информацией:

Название: Интересные заказы
Описание: набор номеров заказов, представляющих интерес для конкретного анализа.
Обязательно: True
Тип: список

После определения этих полей появляется новая сетка, в которой вы можете ввести значения, которые хотите сохранить для своего параметра. В данном случае это значения 125, 777 и 999.

Хотя в этом примере используются числа, вы также можете хранить в своем списке другие типы данных, например текст, даты, дату и время и т. д. Дополнительная информация: типы данных в Power Query

Если вы хотите иметь больший контроль над тем, какие значения используются в вашем параметре списка, вы всегда можете создать список с постоянными значениями и преобразовать запрос списка в параметр, как показано ранее в этой статье.

Установив новые параметры списка «Интересные заказы», вернитесь к запросу «Заказы». Выберите меню автофильтра поля OrderID. Выберите Числовые фильтры > Входящие.

После выбора этого параметра появится новое диалоговое окно "Фильтровать строки". Отсюда вы можете выбрать параметр списка из раскрывающегося меню.

Параметры списка могут работать как с параметрами In In, так и Not In. In позволяет фильтровать только значения из вашего списка. Not in делает прямо противоположное и пытается отфильтровать ваш столбец, чтобы получить все значения, которые не равны значениям, хранящимся в вашем параметре.

После нажатия кнопки "ОК" вы вернетесь к своему запросу. Там ваш запрос был отфильтрован с использованием созданного вами параметра списка, в результате чего были сохранены только строки, где OrderID был равен 125, 777 или 999.

Вы можете настроить запрос параметра Microsoft Query, изменив подсказку, используя данные из ячейки в качестве подсказки или используя константу.

Дополнительные сведения о создании запросов с параметрами см. в статье Использование Microsoft Query для извлечения внешних данных.

Примечание. Следующие процедуры не применяются к запросам, созданным с помощью Power Query.

Изменить пользовательское приглашение для запроса с параметрами

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

На вкладке "Данные" в группе "Запросы и подключения" нажмите "Свойства".

В диалоговом окне "Свойства" нажмите "Свойства подключения" .

В диалоговом окне "Свойства подключения" перейдите на вкладку "Определение" и нажмите "Параметры".

В диалоговом окне "Параметры" в списке "Имя параметра" щелкните параметр, который нужно изменить.

В поле Запрашивать значение, используя следующую строку, введите текст, который вы хотите использовать для подсказки, а затем нажмите кнопку ОК. Пользовательское приглашение может содержать до 100 символов.

Чтобы использовать новое пользовательское приглашение и обновить данные, нажмите стрелку рядом с кнопкой "Обновить все" на вкладке "Данные" в группе "Запросы и подключения" и нажмите "Обновить".

В диалоговом окне "Введите значение параметра" отображается новое приглашение.

Примечание. Чтобы предотвратить повторный запрос во время каждой операции обновления, можно установить флажок Использовать это значение/ссылку для будущих обновлений. Если вы используете данные из ячейки в качестве значения параметра, установите флажок Обновлять автоматически при изменении значения ячейки.

Введите на листе значения, которые вы хотите использовать в качестве критериев в запросе.

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

На вкладке "Данные" в группе "Запросы и подключения" нажмите "Свойства".

В диалоговом окне "Свойства" нажмите "Свойства подключения" .

В диалоговом окне "Свойства подключения" перейдите на вкладку "Определение" и нажмите "Параметры".

В диалоговом окне "Параметры" в списке "Имя параметра" щелкните параметр, который нужно изменить.

Нажмите Получить значение из следующей ячейки.

На листе щелкните ячейку, содержащую значение, которое вы хотите использовать.

Если вы хотите обновлять данные при каждом изменении значения в ячейке, установите флажок Обновлять автоматически при изменении значения ячейки.

Чтобы обновить данные, нажмите стрелку рядом с кнопкой "Обновить все" на вкладке "Данные" в группе "Запросы и подключения" и нажмите "Обновить".

На листе щелкните ячейку в диапазоне внешних данных, созданном с помощью запроса.

На вкладке "Данные" в группе "Запросы и подключения" нажмите "Свойства".

В диалоговом окне "Свойства" нажмите "Свойства подключения" .

В диалоговом окне "Свойства подключения" перейдите на вкладку "Определение" и нажмите "Параметры".

В диалоговом окне "Параметры" в списке "Имя параметра" щелкните параметр, который нужно изменить.

Нажмите Использовать следующее значение.

Введите значение, которое вы хотите использовать для параметра, а затем нажмите OK.

Изменить пользовательское приглашение для запроса с параметрами

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

На вкладке "Данные" в группе "Подключения" нажмите "Свойства".

В диалоговом окне "Свойства" нажмите "Свойства подключения" .

В диалоговом окне "Свойства подключения" перейдите на вкладку "Определение" и нажмите "Параметры".

В диалоговом окне "Параметры" в списке "Имя параметра" щелкните параметр, который нужно изменить.

Чтобы использовать новое пользовательское приглашение и обновить данные, нажмите стрелку рядом с кнопкой «Обновить все» на вкладке «Данные» в группе «Подключения». Затем нажмите «Обновить».

В диалоговом окне "Введите значение параметра" отображается новое приглашение.

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

Введите на листе значения, которые вы хотите использовать в качестве критериев в запросе.

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

На вкладке "Данные" в группе "Подключения" нажмите "Свойства".

В диалоговом окне "Свойства" нажмите "Свойства подключения" .

В диалоговом окне "Свойства подключения" перейдите на вкладку "Определение" и нажмите "Параметры".

В диалоговом окне "Параметры" в списке "Имя параметра" щелкните параметр, который нужно изменить.

Нажмите Получить значение из следующей ячейки.

На листе щелкните ячейку, содержащую значение, которое вы хотите использовать.

Чтобы обновить данные, нажмите стрелку рядом с кнопкой "Обновить все" на вкладке "Данные" в группе "Подключения", а затем нажмите "Обновить".

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