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

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

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

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

Внимание

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

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

Примечание.

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

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

Начните с использования соединителя папок, чтобы перейти к папке, в которой находятся файлы, и выберите "Преобразовать данные " или "Изменить". Эти действия поступают в power Query. Щелкните правой кнопкой мыши двоичное значение в поле "Содержимое" и выберите параметр "Добавить как новый запрос". В этом примере был выбран первый файл из списка, который будет файлом 2019.csv апреля.

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

Создайте новый параметр с именем "Параметр файла" и типом binary. Используйте запрос примера файла в качестве значения по умолчанию и текущего значения.

Примечание.

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

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

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

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

Щелкните правой кнопкой мыши параметр файла в области "Запросы". Выберите параметр "Ссылка".

Переименуйте только что созданный запрос из параметра файла (2) в преобразование примера файла.

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

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

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

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

Применение преобразований к образцу запроса

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

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

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

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

1. Удалите первые четыре строки. Это действие избавляется от строк, которые считаются частью раздела заголовка файла.

   

   Примечание.

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

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

   

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

Примечание.

Дополнительные сведения о повышении и понижении заголовков см. в разделе "Повышение или понижение заголовков столбцов".

Внимание

Функция файла преобразования зависит от шагов, выполняемых в запросе на пример файла преобразования. Однако если вы попытаетесь вручную изменить код для функции преобразования файлов , вы будете приветствовать предупреждение, которое считывает The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

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

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

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

Примечание.

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

Функция была применена к каждой строке из таблицы, используя значения из столбца Content в качестве аргумента для функции. Теперь, когда данные преобразуются в нужную фигуру, можно развернуть столбец выходной таблицы , выбрав значок "Развернуть". Не используйте префикс для развернутых столбцов.

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

Примечание.

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

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

Добавление нового параметра в существующую пользовательскую функцию

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

Чтобы сделать это требование, создайте новый параметр с именем Market с текстовым типом данных. В поле "Текущее значение" введите значение Панама.

В этом новом параметре выберите запрос "Преобразовать пример файла " и отфильтруйте поле "Страна " с помощью значения из параметра Market .

Примечание.

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

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

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

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

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

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

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

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

кодом
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Сначала у вас есть параметр, имеющий значение, которое служит примером. В этом случае это значение PTY-CM1090-LAX.

В этом параметре создается новый запрос, в котором применяются необходимые преобразования. В этом случае необходимо разделить код PTY-CM1090-LAX на несколько компонентов:

• Источник = PTY
• Назначение = LAX
• Авиакомпания = CM
• FlightID = 1090

Следующий код M демонстрирует этот набор преобразований.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Примечание.

Дополнительные сведения о языке формул Power Query M см. на языке формул Power Query M.

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

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