Функциональный запрос и операторы в редакторе запросов

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

Оглавление

• Создание функционального запроса
• Вызов пользовательского функционального запроса
  • Удаление аргумента
  • Добавление аргумента
• Понимание унарных функций
  • Написание кода в функциональном запросе
• Заключение

Создание функционального запроса

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

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

В этом примере параметры — это a и b , а тело функции — это a + b . Назовите запрос Add2Values ​​.

Вот как выглядит запрос функции.

Рядом с именем запроса на панели запросов вы можете увидеть значок fx , который указывает, что это запрос функции.

Чтобы вызвать функцию, введите значение для каждого параметра и нажмите Invoke .

Нажатие Invoke создаст новый запрос с именем Invoked Function , который содержит результат заданных параметров. В строке формул вы также увидите, что она ссылается на запрос функции по имени и присваивает значения параметрам.

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

Вызов пользовательского функционального запроса

Чтобы вызвать пользовательскую функцию для каждой строки таблицы, вы можете перейти на вкладку «Добавить столбец» и выбрать « Вызвать пользовательскую функцию» .

Другой способ — щелкнуть значок мини-таблицы в верхнем левом углу предварительного просмотра и выбрать «Вызвать пользовательскую функцию».

Если щелкнуть «Вызвать пользовательскую функцию», появится диалоговое окно. Введите AddValues ​​в качестве имени столбца и выберите Add2Values ​​в качестве функционального запроса. Затем установите значения для каждого параметра.

В этом примере значения находятся в столбцах 1 и 2.

Вы видите, что в таблицу добавлен новый столбец. Значения внутри столбца представляют собой сумму значений строк столбцов 1 и 2.

Удаление аргумента

Если вы удалите один из аргументов внутри формулы, значения внутри нового столбца приведут к ошибке . В этом примере Column2 удаляется из формулы.

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

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

Для следующего шага вернитесь к запросу функции Add2Values ​​и откройте окно расширенного редактора. Затем добавьте необязательное ключевое слово в скобках и нажмите «Готово».

Если вы вернетесь к SumExample Table Query , вы увидите, что значения Error в последнем столбце превращаются в нулевые значения. Применение оператора к значениям, включающим нуль, всегда будет возвращать ноль.

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

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

Добавление аргумента

Добавление слишком большого количества аргументов также приведет к ошибкам. Если вы введете столбцы 2 и 3 в строке формул, в последнем столбце будут отображаться значения ошибок.

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

Существует функция М , которая помогает справиться с такой ситуацией. Сначала создайте новый пустой запрос и введите функцию Function.From в строку формул. Затем вы увидите документацию по функции.

Для демонстрации продублируйте запрос функции Add2Values ​​и откройте окно расширенного редактора. Затем введите Function.From в начале синтаксиса.

В качестве первого аргумента введите function как тип функции. В качестве второго аргумента введите List.Sum , чтобы просуммировать значения и вернуть список. После этого нажмите «ОК» и переименуйте запрос в «AddValues» .

Затем вернитесь к SumExample Table Query и измените Function Query на AddValues . Вы увидите, что столбец AddValues ​​теперь имеет сумму значений каждой строки столбца.

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

Как вы называете свои параметры, не имеет значения.

Если вы пишете пользовательскую функцию в Function.From и вам нужно сослаться на элемент, вы должны использовать оператор позиционного индекса для доступа к элементу в списке.

Понимание унарных функций

Унарные функции — это функции, которые вы постоянно видите. Многие стандартные библиотечные функции принимают функции в качестве аргументов, и эти функции параметров часто являются унарными. Это означает, что функция принимает только один единственный аргумент.

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

После этого вы увидите таблицу со столбцами CustomerID и Name на панели предварительного просмотра. Назовите запрос FilterExample .

Написание кода в функциональном запросе

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

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

Другой способ — использовать ключевое слово each , которое является сокращением для унарной функции. Он принимает одну безымянную переменную в качестве аргумента и представлен символом подчеркивания ( _ ). Чтобы продемонстрировать, откройте окно расширенного редактора и измените пользовательскую функцию.

Как только вы нажмете «Готово», вы увидите, что он дает те же результаты.

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

Если вы вернетесь в окно расширенного редактора и удалите подчеркивание в пользовательской функции, она по-прежнему будет возвращать те же результаты.

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

Функция M для таблицы дат — как добавить параметр в редакторе запросов
Создание пользовательской функции очистки текста в Power Query

Заключение

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

Мелисса