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

Если вы найдете себя в ситуации, когда необходимо применить один набор преобразований к разным запросам или значениям, создайте пользовательскую функцию 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.

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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'.

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

После создания пользовательской функции и всех шагов преобразования, которые были включены, вы можете вернуться к исходному запросу, где у вас есть список файлов из папки. На вкладке "Добавить столбец " на ленте выберите "Вызвать настраиваемую функцию " из группы "Общие ". В окне "Вызов настраиваемой функции" введите в качестве имени нового столбца таблицу вывода. Выберите имя функции, файл преобразования в раскрывающемся списке запроса функции. После выбора функции в раскрывающемся меню будет отображаться параметр функции, и вы можете выбрать столбец из таблицы, который будет использоваться в качестве аргумента для этой функции. Выберите столбец 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.

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

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