Глава 9. Параметры и пользовательские функции

Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М Power Query. Язык M – это функциональный язык, содержащий сотни функций, подходящих для решения самых разных задач. На первых порах стандартная библиотека удовлетворит большинство ваших потребностей. Однако, как только вы столкнетесь с более сложными ситуациями, вы обнаружите, что возможность писать пользовательские функции открывает новые горизонты. Пользовательские функции упрощают процесс преобразования данных и дают возможность реализовать сложную логику. Особенно полезно превращение существующих запросов в динамические, многократно используемые функции. Вы можете настроить логику один раз и применять ее во многих местах. Если позже вам понадобится изменить логику, просто обновите функцию. Изменение автоматически распространится на все запросы, использующие эту функцию.

Мои комментарии набраны с отступом.

Предыдущая глава       Содержание    Следующая глава

Скачать заметку в формате Word или pdf

В этой главе мы рассмотрим следующие вопросы:

• Параметры
• Что такое пользовательская функция
• Преобразование запроса в функцию
• Вызов пользовательской функции
• Выражение each
• Ссылки на имена столбцов и полей
• Отладка пользовательской функции
• Область действия функции

Рекомендуем выполнять примеры в редакторе Power Query. Так вы получите более глубокое их понимание. Исходные файлы включены в репозиторий GitHub.

Параметры

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

Общие сведения о параметрах

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

Типичные сценарии использования параметров обеспечивают гибкость, позволяя:

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

Еще одно преимущество параметров заключается в том, что их значения можно изменять вне редактора Power Query. Так как же это работает?

Интерфейс Power BI Desktop дает возможность изменять параметры, не открывая Power Query. Перейдите на вкладку Главная, щелкните раскрывающийся список под кнопкой Преобразование данных и выберите Изменить параметры. Помните, что вам нужно будет обновить набор данных, чтобы изменения в параметрах вступили в силу.

Рис. 9.1. Изменение параметров в Power BI Desktop

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

• Перейдите на https://app.powerbi.com.
• Перейдите к соответствующей рабочей области и выберите Параметры набора данных.
• Перейдите в раздел Параметры.

Рис. 9.2. Изменение параметров наборов данных в службе Power BI

Для параметров в службе Power BI изменения также становятся видимыми только после обновления данных.

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

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

Откройте редактор Power Query, пройдите Главная –> Управление параметрами –> Создать параметр. Откроется окно Управление параметрами:

Рис. 9.3. Создание параметра в окне Управление параметрами

Окно Управление параметрами предоставляет дополнительные сведения о параметрах, а также способы определения типа значений. Ниже приведено объяснение каждого из свойств:

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

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

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

Тип – задает тип данных параметра. Указав тип (например, текст, число или дата), вы гарантируете, что параметр будет принимать только значения этого типа. Это важно для обеспечения целостности данных и избежания неожиданных ошибок при выполнении запроса.

Предлагаемые значения – дает рекомендации о значениях, которые может принимать параметр. Существует три варианта:

• Любое значение; параметр может принимать любое значение указанного типа.
• Список значений; определяет заранее заданный набор значений, которые может принимать параметр. Например, если у вас есть параметр, который должен принимать значения Низкий, Средний или Высокий, вы используете этот вариант.
• Запрос; позволяет задавать значения на основе результата запроса. Это полезно, когда допустимые значения для параметра являются динамическими и могут изменяться на основе другого источника данных или логики. Например, вы можете предоставить уникальный список категорий продуктов из базы данных.

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

После настройки параметр добавляется на панель Запросы. Пользовательский интерфейс автоматически добавляет три поля метаданных:

• IsParameterQuery – всегда имеет значение true, указывая, что это параметр.
• IsParameterQueryRequired – true или false в соответствии с тем стоит ли галка в поле Требуется.
• Type – указывает тип данных параметра.

Например, при вводе даты Power Query создаст запрос Example_Parameter. В расширенном редакторе для него отображает код и метаданные:

Рис. 9.4. Метаданные параметра

Параметры можно создавать не только в пользовательском интерфейсе, но и в расширенном редакторе. Знание синтаксиса позволит вам программно добавлять параметры (например, с помощью Tabular Editor 3), или вручную вставить код параметра в расширенный редактор. Подробнее о метаданных можно прочитать в главе 7 Концепция M.

Теперь, когда вы знаете, как создавать параметры, давайте изучим, как использовать параметры в запросах.

Использование параметров в запросах

Параметры можно интегрировать в код M, ссылаясь на них, подобно тому, как вы ссылаетесь на таблицу или шаг в запросе. Обратимся к примеру:

Рис. 9.5. Набор счетов

Здесь указаны номера счетов-фактур и сумма без НДС. Цель – создать новый столбец Сумма с НДС. Ставку НДС мы хотим указать с помощью параметра для удобства ее изменения. Во-первых, нужно создать параметр, как описано в предыдущем разделе. Откройте окно Управление параметрами, назовите параметр VAT_Percentage, задайте тип Десятичное число, установите Текущее значение 0,25 (что соответствует ставке НДС = 25%):

Рис. 9.6. Свойства параметра VAT_Percentage

Теперь, когда параметр готов, можно создать настраиваемый столбец. Для этого пройдите Добавление столбца –> Настраиваемый столбец. Настройте опции:

Рис. 9.7. Ссылка на параметр в формуле

Чтобы включить параметр в выражение, сошлитесь на него, как на любую другую таблицу или имя шага. Если имя параметра содержит специальные символы или пробелы, будьте осторожны. В таких случаях важно использовать определенную нотацию, чтобы убедиться, что язык M интерпретирует имя правильно. Используйте формат #"<Имя параметра>". Например, если ваш параметр называется VAT Rate %, вы должны ссылаться на него как #"VAT Rate %". Чтобы поддерживать чистоту кода, мы рекомендуем использовать имена переменных, начинающиеся с буквы и состоящие только из букв, цифр и символов подчеркивания. Следуя этому соглашению, нет необходимости в нотации с кавычками.

Например, можно использовать camelCase

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

Параметр с информацией о подключении

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

Этот пример предназначен только для чтения. Тем не менее, если у вас есть доступ к базе данных, рекомендуем попробовать на ней. Для подключения к базе данных SQL необходимо указать имя сервера и базы данных:

Вторая строка кода – имя сервера, третья – базы данных. Обратите внимание на dev в обоих компонентах. При настройке баз данных администраторы стараются упростить переход между средами. Вам нужно заменить dev на test для тестовой среды или prod для продуктива. Вместо того, чтобы жестко кодировать имя сервера, можно использовать параметр.

Откройте окно Управление параметрами и создайте новый параметр. Назовите его Environment, убедитесь, что установлен флажок Требуется, определите тип text, в Предлагаемых значениях выберите Список значений. Укажите три доступных значения: dev, test и prod. Нажмите OK.

В запросе найдите строку, в которой обращаются к базе данных на сервере (код 9.1). Замените dev параметром Environment:

Здесь параметр Environment функционирует как заполнитель, который позволяет легко изменить строку подключения. Сохранив это изменение и отправив файл в службу Power BI, вы получаете возможность изменять значение параметра непосредственно из меню параметров набора данных. Это означает, что вы можете переключаться между базами, даже не открывая Power BI Desktop.

Динамические пути к файлам

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

Предположим, вы создаете обучающие материалы, которые включают в себя файл Avocado Prices.csv. Вы решаете сохранить этот файл на диске C, в папке с именем Data. Чтобы подключиться к этому файлу из Power Query, пройдите Главная –> Создать источник –> Текстовый или CSV-файл. Перейдите в папку C:\Data и выберите файл. Кликните Открыть. Откроется окно предварительной загрузки Avocado Prices.csv. Кликните OK. Сгенерится код:

Обратите внимание, что путь к файлу, C:\Data\Avocado Prices.csv, встроен в код. Это удобно для вас, но вряд ли всем. Что если ваши ученики хранят файлы в разных местах? Что делать, если вы создали другие запросы, которые также используют этот путь к файлу в качестве ссылки?

Жесткое кодирование пути к файлу быстро становится камнем преткновения, требующим утомительного ручного обновления каждым человеком. К счастью, вы можете использовать параметры, чтобы обеспечить гибкость путей к файлам. Начните с создания параметра текстового типа CSVFilePath с Текущим значением C:\Data\Avocado Prices.csv.

При первоначальном обращении к CSV-файлу (код 9.2) в области Примененные шаги был сформирован шаг с названием Source. Для интеграции параметра нажмите на значок шестеренки рядом с шагом Source. В открывшемся окне в поле Путь к файлу выберите Параметр, а затем CSVFilePath. Нажмите OK.

Рис. 9.10. Вы можете использовать параметр в качестве пути к файлу

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

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

Профессиональный совет. Чувствуете себя уверенно с кодом M? Обойдите интерфейс и измените код напрямую, сославшись на CSVFilePath в строке формул или в расширенном редакторе.

Фильтрация диапазона дат

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

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

Здесь первый аргумент ссылается на таблицу DataTable для фильтрации, а второй – фильтрует столбец Date.

Всякий раз, когда ваш фокус смещается на новый квартал, все, что вам нужно изменить, – это параметры StartDate и EndDate. Однако есть одна загвоздка: хотя описанный выше метод позволяет легко настроить значения параметров, вы все равно должны вручную установить StartDate и EndDate для каждого квартала. Разве не было бы неплохо снабдить параметр динамическим значением?

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

Например, подумайте о ситуации, когда вы хотите отфильтровать данные за недавно завершившийся квартал. Создайте запрос…

… и назовите его EndDate.

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

Для StartDate можно сослаться на EndDate и сдвинуть его на начало квартала:

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

Пользовательские функции

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

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

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

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

Что такое пользовательские функции?

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

Пользовательскую функцию можно определить, указав параметры в круглых скобок, за которыми следует знак перехода => и выражение функции. Выражение определяет, как функция должна вычислять результат. Давайте рассмотрим простую функцию, которая прибавляет 10 к значению:

Вот как это интерпретировать:

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

=> – символ перехода; отделяет параметры функции от ее выражения.

parameter + 10 – выражение (или тело функции), которое указывает, как функция должна обрабатывать входные данные для получения выходного значения.

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

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

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

До сих пор в примерах выражение функции записывалось без использования каких-либо переменных. Однако, если вы хотите внести дополнительную ясность в свои функции, рассмотрите возможность интеграции выражения let. Вот как это можно сделать:

Эта функция преобразует текстовую строку в число, умножает его на 3 и предоставляет пользовательскую строку в качестве выходных данных. Таким образом, если вы назовете функцию fxMultiplyText и вызовете ее с помощью fxMultiplyText("5"), она вернет строку: "3 times 5 is 15".

Если при вызове функции вы столкнулись с ошибкой, убедитесь, что код в строке формул выглядит так: fxMultiplyText("5"), где число 5 заключено в одну пару кавычек.

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

Функция вернет значение типа datetime, соответствующее локальному дата-времени на вашем компьютере. Вы также можете создать пользовательскую функцию без параметров. Например, вы можете вернуть значение радиуса Земли, написав:

Эта функция не принимает параметров и возвращает числовое значение. Такое использование встречается редко. Лучше сохранить значение в виде параметра или переменной.

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

Преобразование запросов в функцию

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

Что такое функционал создать функцию?

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

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

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

Создание функции из запроса имеет три преимущества:

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

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

• Если вам нужны параметры, включите их в запрос. Это может быть что-то простое, например, критерий фильтра или значение, которое участвует в вычислении.
• Перейдите на панель Запросы, расположенную в редакторе Power Query слева, и щелкните правой кнопкой мыши имя запроса.
• Нажмите Создать функцию. Откроется диалоговое окно.
• Дайте функции описательное имя, чтобы было легко понять, что она делает.
• Нажмите ОK.

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

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

Этот запрос создает столбец с датами и добавляет столбцы Year и Month. Вы хотите, чтобы пользователи могли управлять календарем. Поэтому создаете параметры startDate и endDate типа date. Запрос CalendarLogic теперь выглядит так:

Теперь, когда параметры установлены, мы готовы преобразовать запрос в функцию. Для этого кликните правой кнопкой мыши CalendarLogic и выберите Создать функцию. Задайте имя функции fxCreateCalendar. Нажмите OK. Power Query преобразует запрос в функцию. Выполнив эти операции, у вас будет четыре запроса: параметры startDate и endDate, запрос CalendarLogic с логикой календаря, функция fxCreateCalendar, связанная с запросом CalendarLogic:

Рис. 9.15. Компоненты, задействованные в операции Создать функцию

Теперь, когда функция настроена, посмотрим, как ее использовать. Для этого создайте новый запрос, переименуйте его в Calendar и введите код:

Рис. 9.16. Использование функции для создания таблицы Calendar

Таким образом, с помощью одной строки кода, использующей функцию, вы получаете календарь с ранее определенной логикой. Разве это не здорово? Готовое решение можно найти в файлах упражнений, прилагаемых к главе.

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

Устранение неполадок и внесение изменений

Прелесть операция Создать функцию заключается в том, насколько легко вы можете вносить изменения. Допустим, вам нужно добавить столбец, в котором указан номер дня. Достаточно перейти к исходному запросу CalendarLogic и вставить туда новый столбец – Day:

Рис. 9.17. Динамическая ссылка: совместное обновление запроса и пользовательской функции

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

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

Рис. 9.18. Сообщение при попытке открыть связанную функцию в расширенном редакторе

Если вы просто хотите проверить код, можно безопасно нажать OK и посмотреть. Убедитесь, что вы не сохранили никаких изменений, и ссылка продолжит действовать. Если вы хотите остановить обновления между запросом и функцией, вы можете щелкнуть функцию правой кнопкой мыши и выбрать Свойства… Нажмите Остановить обновления:

Рис. 9.19. Свойства функции, обновляемой при изменении запроса

Будьте осторожны. После нажатия Остановить обновления нет способа восстановить обновления для текущей функции. Вам придется создать из запроса новую функцию.

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

Вызов функции в расширенном редакторе или строке формул

Большинство продвинутых пользователей вызывают функцию, ссылаясь на нее напрямую. Предположим, вы создали функцию MyFunction, которая принимает одно числовое значение и возвращает это число плюс 10. Вы создаете пустой запрос и вставляете MyFunction( 20 ) в строку формул:

Рис. 9.20. Вызов функции в строке формул

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

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

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

Рис. 9.22. Меню вызова пользовательских функций

В раскрывающемся списке может быть больше вариантов:

• Введите значение (Enter a value): позволяет ввести значение вручную. Это наименее динамичный вариант.
• Используйте значения в столбце (Use values in a column): позволяет выбрать столбец, к которому будет применена функция. В раскрывающемся списке предлагается любой столбец таблицы.
• Выберите параметр (Select a parameter): позволяет сослаться на параметр.
• Выберите запрос (Select a query): позволяет сослаться на выходные данные запроса. Имейте в виду, что функция может требовать входных данных определенного типа. При ссылке на другой запрос выходное значение этого запроса должно иметь тип данных, требуемый функцией. Это могут быть примитивные значения (текст или число) или структурированные значения (таблица, запись или список).

Мне не удалось получить такое разнообразие вариантов.

Давайте изучим выражение, которое создает функцию в языке M – выражение each. Оно используется наиболее часто, поскольку Power Query автоматически вставляет его в широкий спектр функций при выполнении операций в пользовательском интерфейсе.

Выражение each

Выражение each в языке Power Query M – это сокращенный синтаксис, который позволяет определять унарные функции (с одним аргументом) без сложностей, связанных с объявлением функции.

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

В функции один параметр, и запись можно упростить:

Теперь параметр представлен символом подчеркивания. Это очень похоже на то, что делает конструкция each. Конструкция each – это синтаксический сахар для функции, которая принимает один параметр с подчеркиванием в качестве имени.

Следующее выражение является еще более упрощенной версией предыдущего:

Объявление each используется для улучшения читаемости вызова функций более высокого порядка. Это полезно, так как ускоряет написание кода и часто уменьшает его длину. Чтобы понять, что это значит, давайте рассмотрим некоторые распространенные варианты использования each.

Вы найдете выражение each в различных функциях: Table.AddColumn, Table.SelectRows и List.Transform. Мы рассмотрим:

• Автоматическое добавление each в пользовательские столбцы
• Использование each в TransformColumns
• Пропуск символов подчеркивания в ссылках на поля или столбцы
• Упрощенный вызов функций с одним аргументом

Автоматическое добавление each в пользовательские столбцы

Представьте, что у вас есть столбец с именем Number, и вы хотите прибавить 5 к каждому значению. Пройдите Добавление столбца –> Настраиваемый столбец и введите формулу:

После нажатия ОK в строке формул появится выражение:

Постойте, код включает ключевое слово each! Как оно там появилось, если мы не указали его в формуле настраиваемого столбца?

Когда вы добавляете настраиваемый столбец с помощью пользовательского интерфейса, движок выполняет часть работы за вас. Power Query автоматически вызывает функцию Table.AddColumn и добавляет ключевое слово each в формулу. Вам не нужно вставлять each вручную. Это полезно, потому что концепция each часто сбивает с толку начинающих. Давайте посмотрим, как эта концепция применяется к функции Table.TransformColumns.

Использование each в Table.TransformColumns

Функция Table.TransformColumns используется для управления значениями столбцов. При вызове функции через интерфейс Power Query вы часто будете видеть ключевое слово each. Представьте, что вы хотите добавить префикс Miss к значению в столбце Name. Пройдите Преобразование –> Формат –> Добавить префикс и введите Miss (с пробелом). Движок сгенерит код:

Это выражение функционально эквивалентно:

Обратите внимание на то, как отличаются два примера кода. Ключевым моментом является понимание того, как работает функция Table.TransformColumns. Для нее необходимо определить конкретный способ изменения каждого значения в столбце. И в игру вступает параметр function, представляющий каждое значение в изменяемом столбце. В первом подходе мы используем each. Во втором более явное определение функции (_) =>.

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

Пропуск символов подчеркивания в ссылках на поля или столбцы

Когда вы имеете дело с записями или полями таблицы можно опустить символ подчеркивания. Допустим, вы хотите добавить столбец, который вычитает 10 из значений в существующем столбце Sales. Полная форма кода:

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

Проверить, что возвращает знак подчеркивания, можно с помощью функции (_) => _. Например: Table.AddColumn(Source, «Custom», (_) => _ )

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

Пользовательский интерфейс Power Query еще больше упрощает задачу с помощью конструкции each:

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

Упрощение вызовов функций с одним аргументом

Многие функции позволяют включить другую функцию в качестве аргумента. Способ сделать это зависит от количества аргументов, требуемых вложенной функцией. При работе с функцией с одним аргументом все может быть довольно просто. Скобки можно опустить и напрямую сослаться на имя функции. Рассмотрим следующий пример, в котором мы добавляем 10 к каждому элементу в списке:

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

Для функции с несколькими параметрами можно использовать конструкцию each или традиционное определение функции. Например:

Тюнинг функций

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

Далее мы рассмотрим два метода улучшения пользовательских функций:

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

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

Указание типов данных

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

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

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

Примитивные типы

Как мы можем присвоить параметру примитивный тип данных? Представьте, что у нас есть столбец с возрастом людей, и вы хотите создать функцию с именем fxValidAge. Функция проверяет адекватность возраста в предположении, что это число от 0 до 125:

Здесь возраст является явным параметром, поскольку определен его тип. Функция же вернет логическое значение. Ниже в таблице столбец fxValidAge применяет приведенную выше функцию к столбцу Age:

Рис. 9.24. Функциональная проверка допустимости возраста

Указав тип данных входного параметра, мы заставили движок проверять, соответствуют ли входные значения типу данных. На этом этапе все строки возвращают допустимое значение, так как входные возрасты имеют числовой тип. Однако если вы попытаетесь указать текст (например, «20») или любое другое число, Power Query вернет ошибку:

Рис. 9.25. Нечисловое значение приводит к ошибке

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

В качестве выходных данных функция в настоящее время ожидает логическое значение. Если вы измените код на…

… функция также вызовет ошибку Expression.Error: Не удается преобразовать значение true в тип Text.

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

Примитивные типы, допускающие значение null

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

Использование строгого примитивного типа (например, text) для столбца, в котором могут встречаться значения null, может привести к ошибкам. С другой стороны, назначение столбцу типа any часто будет слишком широкой классификацией. Чтобы решить эту проблему, рекомендуется использовать примитивный тип, допускающий значение null. Для этого в языке М существует термин nullable, который означает, что столбец может содержать либо значение указанного примитивного типа, либо значение null. Так как же мы можем это применить?

Вернемся к функции проверки возраста fxValidAge. Предположим, у вас есть набор данных о возрасте клиентов, но некоторые записи отсутствуют. Посмотрим, как вы можете использовать тип nullable для обработки таких ситуаций. Исходная функция (код 9.3) завершится с ошибкой:

Рис. 9.26. Ошибка возникает, когда функция встречает значение null

Функция fxValidAge проверяет, соответствует ли каждое входное значение ожидаемому типу данных. Поскольку функция настроена на прием только чисел, она завершается ошибкой при встрече с null. Чтобы решить проблему, измените тип параметра:

С помощью этого изменения функция будет пытаться вернуть null всякий раз, когда она встречает null в значении столбца. К сожалению, код 9.4 по-прежнему возвращает ошибку Expression.Error: Не удается преобразовать значение null в тип Logical. Причина в том, что результат функции описан как логическое значение. А так как функция пытается вернуть null, когда встречает null в одном из параметров, мы получаем ошибку. Сделайте тип logical допускающим значение null:

Рис. 9.27. Добавление nullable в типы параметра и результата позволяет функции справляться с пропусками в значениях

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

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

Как сделать параметры необязательными

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

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

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

Здесь определена пользовательская функция с двумя аргументами: обязательным и необязательным. Оба аргумента используют тип, допускающий значение null. Оператор объединения с null (??) вернет maxAge, если параметр был указан или значение по умолчанию, если параметр был опущен.

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

Теперь вы можете вызвать эту функцию с одним или двумя параметрами:

В первом случае предоставляется только обязательный параметр. Во втором есть необязательный параметр. Он проверяет, находится ли возраст 100 в диапазоне от 0 до 90 лет. Это не так, и возвращается false. Видя это, пользователь может предпринять альтернативные действия для выбросов. Например, проверить, не было ли ошибки ввода.

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

Ссылки на имена столбцов и полей

Создание пользовательских функций в Power Query включает в себя важный этап – определение параметров. Однако ссылаться на объекты, необходимые для логики функции, не всегда просто. Например, в стандартной формуле можно просто сослаться на столбец Column1. Если же ввести имя столбца в качестве параметра функции, это не сработает.

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

Ссылка на имя поля в записи

Предположим, вы хотите отфильтровать строки таблицы. Функцией по умолчанию для фильтрации строк в таблице является Table.SelectRows. Например, чтобы вернуть строки, в которых сумма продаж меньше 1000, введите:

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

Поскольку в файле Chapter 9 — Parameters and Custom Functions.pbix авторы не предоставили примеры к этому разделу, я написал свой код. Функцию 9.6 я назвал mySelect, и вызвал ее с параметрами:

Функция завершилась ошибкой Expression.Error: Поле «columnName» записи не найдено.

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

Начнем с того, что означает [columnName] в коде 9.6. Это, или имя столбца таблицы, или имя поля записи. Если вы не уверены, вот простой способ проверить это. Замените условие подчеркиванием:

Вызвав функцию, вы получите ошибку. Не страшно, нас интересует текст ошибки. Если это Expression.Error: Не удается преобразовать значение Record в тип Logical, что ж [columnName] – это поле записи.

Чтобы динамически сослаться на столбец, используйте функцию Record.Field. Она принимает два параметра: запись и имя столбца в виде текста. Функцию 9.6 можно переписать:

Удача! Функция фильтрует строки на основе имени столбца, предоставленного пользователем. Решение работает для фильтрации строк, но как действовать, если нужно выбрать столбцы таблицы?

Ссылка на столбцы в таблице

Предположим, вы хотите проверить, существует ли значение в текущей строке таблицы Sales также в столбце Products таблицы Bonus. Если вы добавляете столбец IsBonusProduct в таблицу Sales код может выглядеть так:

В последней строке достаточно указать
each List.Contains(Bonus[Products], [Product])
List.Contains() возвращает тип logical, так что конструкция if…then просто избыточна.

Но что, если вы хотите разрешить пользователю указывать имя таблицы и столбца для проверки. К сожалению, прямая ссылка на столбец невозможна. Для этого следует использовать функцию Table.Column, которая возвращает список из столбца таблицы:

У пользовательской функции четыре параметра:

• table – основная таблица, с которой вы работаете,
• newColumnName – имя нового столбца, который вы добавляете,
• lookupTable – таблица, содержащая список, по которому выполняется проверка,
• lookupColumnName – столбец в lookupTable, содержащий список проверки.

Для непрямого обращения к таблице и столбцу внутри пользовательской функции мы задействовали две функции: Table.Column для выбора столбца и Record.Field для выбора поля. Table.Column извлекает список значений из столбца таблицы подстановки. Затем функция List.Contains проверяет, существует ли в этом списке текущий объект Product, идентифицированный функцией Record.Field. В зависимости от проверки List.Contains возвращает true или false.

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

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

Отладка пользовательских функций

Процесс создания пользовательских функций становится простым, как только вы понимаете основы. Он включает в себя определение параметров, интеграцию их в код, преобразование запроса в функцию. Но что происходит, когда вы сталкиваетесь с ошибкой в пользовательской функции? Или вы адаптируете код, найденный в Интернете? Для функций в области Примененные шаги у вас только один шаг. В игру вступает понимание того, как вести отладку.

Допустим, вы работаете с функцией:

Есть простой способ вернуть функцию в запрос. Для этого создайте переменную для каждого параметра функции и закомментируйте определение функции:

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

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

Область действия функции

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

Выражение верхнего уровня

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

Функцию можно распознать по ее префиксу – символам f_Х в области Запросы. Какое бы имя вы ни дали запросу, оно будет именем вашей функции. Преимущество определения функции на верхнем уровне в том, что ее можно вызывать из других запросов, ссылаясь на ее имя. Тем не менее, вы также можете создавать пользовательские функции внутри запроса.

Встроена в запрос

Думайте об этом подходе так: функция – это выражение, а запрос состоит из последовательности выражений. И, как и в случае с любым другим выражением, в запрос можно включить определение пользовательской функции. Предположим, что вы создали логику в запросе с именем SquareRoots:

На третьем шаге запроса определяется пользовательская функция с именем sqrtDivide. На шаге Applyfunction мы вызываем эту функцию. Т.е., можно применять пользовательскую функцию, даже если она не хранится в виде отдельного запроса. Важно: функции, созданные в запросе, можно использовать только внутри запроса.

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

Как вы можете использовать полученные знания в практических сценариях?

До сих пор мы говорили о способах создания пользовательских функций и применении параметров. Мы изучили компоненты пользовательской функции и узнали, что выражение each является синтаксическим сахаром для унарной функции. Затем мы обратились к типам данных входных параметров и результата функции. И, наконец, рассмотрели, как необязательные параметры обеспечивают дополнительный уровень гибкости.

Мы призываем вас применять эти концепции в своих проектах. Всякий раз, когда некая логика пригодится в нескольких местах, попробуйте передать ее из запроса в функцию. Без практики вы не освоите эти концепции. Рассмотрим несколько примеров, объединяющих эти идеи.

Превращение всех столбцов в текст

Первый сценарий, который мы рассмотрим, – это преобразование столбцов в желаемый тип данных.

Допустим у вас неструктурированный источник данных – файл CSV, Excel или JSON. Поскольку такие источники не содержат метаданные с информацией о типах данных, вы каждый раз должны установить тип вручную. Создадим пользовательскую функцию, которая соответствует нашим требованиям. Вы можете следовать инструкциям, используя файл для этой главы (запросы в папке ColumnsToText).

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

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

Общие сведения о преобразовании типов данных

Допустим, мы работаем с таблицей:

Рис. 9.31. Исходная таблица со столбцами типа any

Все столбцы имеют тип any. Когда вы измените тип первых двух столбцов на text, Power Query сгенерит код:

Функция Table.TransformColumnTypes принимает два параметра: таблицу (Source) и список списков. Вложенные списки содержат пару значений: имя столбца, который нужно изменить, и тип столбца. Код 9.7 правильный, но не гибкий. Он жестко кодирует имена столбцов. Это может привести к ошибкам при изменении имен столбцов или удалении столбцов, а также не поддержит изменение типа в любых столбцах, которые будут добавлены в будущем. Это означает, что автоматически сгенерированный код 9.7 потребует постоянной ручной настройки.

Построение начальной функции

Чтобы создать первую версию функции извлеките имена столбцов в таблице и поместите их в список; замените имя каждого столбца на пару: { имя , тип данных }; используйте этот обновленный список в качестве входных данных для Table.TransformColumnTypes.

Следующий шаг возвращает имена столбцов таблицы Source:

Рис. 9.31а. Список имен столбцов таблицы Source

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

В этом коде первый аргумент ссылается на предыдущий шаг (код 9.8), т.е., на список имен столбцов из исходной таблицы, а второй – предоставляет логику для преобразования каждого элемента в списке. Символ подчеркивания предоставляет имя каждого столбца в списке. Выражение добавляет «, type text» после имени столбца и заключает данные в фигурные скобки, формируя вложенный список:

Рис. 9.31б. Замена имени столбца на пару: имя + тип

Все, что нам осталось сделать, так это передать код 9.9 в аргумент Table.TransformColumnTypes. К этому моменту мы выполнили следующие шаги:

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

Присвоим функции имя fxToText. Теперь, когда вы хотите всем столбцам таблицы присвоить тип text, просто добавьте шаг:

Применение кода к шагу Source присваивает всем столбцам тип text:

Рис. 9.34. Функция fxToText присваивает всем столбцам тип text

Отличное начало. Добавим в функцию опцию исключения столбцов из преобразования в text.

Расширение функции с помощью необязательных параметров

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

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

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

Помните, что произойдет, если пользователь не укажет значение для необязательного параметра? Функция по умолчанию предоставляет значение null. Чтобы функция работала правильно, она должна обрабатывать это значение null. Чтобы управлять значением null, подумайте о том, как остальная часть функции будет использовать исключения. Чтобы исключить некоторые столбцы, укажите:

Однако, если Exclusions имеет значение null, операция List.RemoveItems завершается ошибкой. Чтобы предотвратить ошибку, подсуньте функции пустой список в качестве резервного варианта:

Это выражение возвращает пустой список всякий раз, когда параметр Exclusions = null. Поскольку удаление пустого списка не изменяет исходный список, это решение соответствует нашим потребностям. Объединив все это, получим:

Мы можем применить его к нашей таблице следующим образом:

Рис. 9.36. Функция, которая изменяет тип на text для всех столбцов таблицы, кроме некоторых

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

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

Объединение таблиц на основе диапазонов дат

В этом разделе мы рассмотрим задачу объединения таблиц на основе диапазона дат. Мы будем использовать различные концепции, которые изучили в этой главе. Вам нужно понимать конструкцию each и область видимости, в которой выполняется код. Мы будем использовать это, чтобы ссылаться на различные значения. Разработка функции также потребует понимания, как ссылаться на столбцы и поля, чтобы их можно было использовать в качестве параметров функции. Данные доступны в файле упражнений (запросы в папке DateRange_Join)

Представьте, что вы имеете дело с транзакциями клиентов, привязанными к определенным контрактам, но уникального идентификатора контракта нет. Хотя в Power Query есть различные типы объединения данных, нет возможности соединять таблицы на основе диапазона дат. Хорошая новость заключается в том, что такой кейс – отличная возможность отточить навыки создания пользовательской функции.

Наша функция должна работать в следующих условиях:

• В основной таблице Transactions есть столбец с датами.
• Таблица Contracts содержит даты начала и окончания каждого контракта.
• Типы данных всех столбцов должны остаться неизменными после объединения.

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

Выполнение соединения на основе диапазонов дат

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

Рис. 9.37. Набор данных для соединения по диапазонам дат

Наша цель – обогатить таблицу Transactions данными из таблицы Contract. Простой способ сделать это – добавить новый столбец и использовать функцию Table.SelectRows. Выберите таблицу Transactions, перейдите на вкладку Добавление столбца и кликните Настраиваемый столбец. Назовите новый столбец Contracts. Введите формулу: Table.SelectRows( Contract , each true ). Кликните OK:

Рис. 9.37а. Добавление столбца с таблицей Contract в каждой строке

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

Корректировка формулы под конкретную дату

В функции Table.SelectRows фильтруются таблицы в столбце Contracts. Чтобы отфильтровать  контракты, которые начинаются, например, 1 июля 2023 года, подойдет код:

Обратите внимание код во втором аргументе функции Table.SelectRows. Он указывает, что нам нужны только строки для которых [Start] = #date(2023,7,1). Пока код жестко кодирует дату. Для пользовательской функции мы хотим фильтровать по диапазону дат и делать это динамически.

Динамический фильтр по дате

Заменим жестко закодированную дату динамической ссылкой на столбец Date в таблице Transactions. И здесь придется вспомнить сложную тему – область видимости, рассмотренную в главе 7 Концепция М.

Если вы попытаетесь заменить #date(2023, 7, 1) на [Date], Power Query вернет ошибку Expression.Error: Поле "Date" записи не найдено.

Ключ к успеху заключается в понимании роли выражения each. Первое выражение each Table.SelectRows(…) находится в области видимости столбца Date таблицы Transactions. Однако второе each [Start] = … размещено внутри Table.SelectRows. Это приводит к конфликту.

Помните, что each является сокращением для функции с одним аргументом (_) =>. Это означает, что код содержит две переменные с символом подчеркивания (_) в качестве имени. И всякий раз, когда вы ссылаетесь на имя переменной, которое встречается несколько раз, переменная во внутренней области имеет приоритет над переменной во внешней области.

Чтобы получить доступ как к внутреннему столбцу (Start из таблицы Contracts), так и к внешнему (Date из Transactions), нам нужно превратить одно из выражений each в пользовательскую функцию. Это позволит указать другое имя переменной (не нижнее подчеркивание) и, таким образом, управлять областями видимости.

Работа с областями видимости с помощью пользовательской функции

Функция Table.AddColumn может принимать в качестве параметра пользовательскую функцию для решения проблемы области видимости. Пользовательская функция получит доступ к обеим таблицам Transactions и Contracts:

Здесь параметр x дает доступ к внешнему контексту, где видна таблица Transactions. Он доступен в области действия функции Table.AddColumn. Аналогично мы можем добавить критерий для конечной даты:

Условие фильтрации ссылается на столбец x[Date], который относится к столбцу Date таблицы Transaction. Проблема области видимости решена:

Рис. 9.37б. В добавленном столбце отфильтрованные таблицы

Однако, когда мы раскрываем столбец Contracts, мы теряем типы данных. Как их сохранить?

Решение проблем с типами данных

У нас есть таблицы с отфильтрованными строками в каждой строке столбца Contracts. Однако в столбце отсутствует указанный тип данных:

Рис. 9.38. В столбце объединения Contracts отсутствует тип данных

Как указать правильные типы данных? Если вводить их вручную, пользовательская функция лишается смысла. Тем не менее, в таблице Contract уже есть правильные типы данных. Может быть, мы можем сослаться на них и предоставить их в новой колонке?

Получите типы данных из другой таблицы с помощью функции Value.Type, которая сошлется на таблицу Contract:

Теперь Table.AddColumn включает четвертый аргумент. Он динамически выбирает типы данных из таблицы Contract. Новый столбец не только корректно фильтрует данные, но и содержит соответствующий тип данных из таблицы Contract.

Теперь преобразуем эту логику в функцию.

Превращение логики в функцию

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

• таблица, в которую добавляется столбец (Table)
• столбец для сравнения дат (Date)

Как обсуждалось ранее в этой главе, функция Record.Field способна принять текстовое значение в качестве аргумента и сослаться на столбец:

Этот код успешно ссылается на поле Date таблицы Transactions. Добавим еще три параметра:

• присоединяемую таблицу (joinTable)
• имя нового столбца, в котором происходит соединение (newColumnName)
• даты начала и окончания, используемые в соединении (startDate и endDate)

Параметр joinTable заменяет жестко закодированную таблицу Contract, а newColumnName –закодированное имя столбца "Contracts". Наконец, startDate и endDate используют Record.Field для ссылки на столбец.

Теперь у нас есть полноценная пользовательская функция – fxDateRangeJoin. Когда вы применяете ее к таблице Transactions, вы получаете столбец с отфильтрованными таблицами:

Рис. 9.39. Работа пользовательской функции для объединения по диапазонам дат

Нам осталось только развернуть столбец Contracts:

Рис. 9.40. Таблица, обогащенная данными

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

Саммари

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

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

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

В следующей главе мы изучим еще одну важную тему. Вы узнаете, как работать с датами, временем и длительностями. Эти типы значений часто встречаются в импортируемых данных. Более того, почти для каждой модели Power BI требуется хороший Календарь. Таким образом, изучение того, как работать с датами бесценно и будет полезным в самых разных сценариях.