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

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

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

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

Важный

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

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

Заметка

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

1. Используйте интерфейс соединителя для подключения к данным, где размещены ваши данные. Выбрав данные, нажмите кнопку Преобразовать данные или Изменить. Это позволит вам воспользоваться интерфейсом Power Query.
2. Щелкните правой кнопкой мыши пустое место в области запросов слева.
3. Выберите пустой запрос.
4. В новом окне пустого запроса выберите меню главная, а затем расширенный редактор.
5. Замените шаблон пользовательской функцией. Например:

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Выберите Готово.

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

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

Заметка

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

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

Начните с использования функции работы с папками, чтобы перейти к папке, в которой находятся ваши файлы, и выберите Трансформировать данные или Редактировать. Эти шаги приведут вас к работе с Power Query. Щелкните правой кнопкой мыши по двоичному значению на ваш выбор из поля Content и выберите опцию Добавить как новый запрос. В этом примере был выбран первый файл из списка, который может быть файлом апреля 2019.csv.

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

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

Заметка

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

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

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

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

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

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

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

Заметка

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

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

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

Заметка

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

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

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

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

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

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

Заметка

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

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

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

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

Теперь вы можете вернуться к развернутой выходной таблице на применённых шагах. Проверьте запрос, чтобы убедиться, что только строки, где страна равна Панама, отображаются в окончательном результирующем наборе запроса 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
• Идентификатор рейса = 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 formula language.

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

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